Work

June 2004 Blog Posts

You've probably already heard by now about SQL Server 2005 Express Edition. It's the next evolution of MSDE 2000.
Here's the Top 10 Cool Things about SQL Server 2005 Express Edition:
Integrated common language runtime (CLR) support. Stored procedures and functions can be written using your favorite programming language.
Native XML support.
XCopy support for moving databases from one location to another.
Robust and reliable installation using either graphical user interface (GUI) or silent modes.
Automated servicing and patching.
Deep integration with Visual Studio to provide access to rich data controls like the DataGridView, DataNavigator, and DataConnector.
Support for databases up...

EDIT: This stored procedure has been updated.
The below stored procedure runs DBCC INDEXDEFRAG for each of the indexes in the user database that is inputted into the sproc. I recommend putting it into an Admin database (hey just name it Admin!) rather than in master. Then just schedule isp_DBCC_INDEXDEFRAG for each of the databases you want defragged. Remember that DBCC INDEXDEFRAG isn't as good as DBCC DBREINDEX in large scale environments (for more information on this, see this article), so see my DBCC DBREINDEX weblog coming soon. It'll be better than the other one I posted a while back.
CREATE PROC isp_DBCC_INDEXDEFRAG(@dbName SYSNAME)ASSET...

EDIT: This stored procedure has been updated.
The below stored procedure will backup the transaction log for each of the databases that aren't being log shipped (you don't want to break log shipping by running a transaction log backup outside of the log shipping plan) and also aren't using SIMPLE for the recovery model. It has one input parameter, which is the path to where you would like to backup the databases. It'll create a sub directory underneath that path for each of the databases. It also deletes any files that are older than two days in that sub directory. If a...

EDIT: This stored procedure has been updated.
Yesterday, I posted a stored procedure that performs a full backup on the databases. I mentioned that I had a SQL LiteSpeed version. This version backs up all databases except master, model, msdb, tempdb, pubs, NorthWind. It has one input parameters, which is the path to where you would like to backup the databases. It'll create a sub directory underneath that path for each of the databases. It also deletes any files that are older than two days in that sub directory. If a sub directory doesn't exist, it creates one for you. It...

EDIT: This stored procedure has been updated.
This stored procedure performs a full backup of the databases. It has two input parameters. The first input parameter is the path to where you would like to backup the databases. It'll create a sub directory underneath that path for each of the databases. It also deletes any files that are older than two days in that sub directory. If a sub directory doesn't exist, it creates one for you. The second input parameter is to control which databases you want to backup. You can pass it All, System, or User. All means backup...

EDIT: This stored procedure has been updated.
Here's a stored procedure that will check the integrity of each of the databases. I've got it scheduled to run every night on each of our servers. I recommend putting it into an Admin database (hey just name it Admin!) rather than in master.
CREATE PROC isp_DBCC_CHECKDB
AS
SET NOCOUNT ON
DECLARE @dbid INT
DECLARE @DBName SYSNAME
DECLARE @SQL NVARCHAR(4000)
SET @dbid = 0
WHILE @dbid < (SELECT MAX(dbid) FROM master.dbo.sysdatabases)
BEGIN
SELECT TOP 1 @dbid = dbid, @DBName = name
FROM master.dbo.sysdatabases
WHERE dbid > @dbid
ORDER BY dbid
SET @SQL = 'DBCC CHECKDB(' + @DBName + ')'
EXEC sp_executesql @statement = @SQL
END
RETURN 0
GO