Very useful SQL statement: 1. Database field list Select (Case When a.colorder = 1 THEN D.NAME ELSE '' END) N 'Name', A.COLORDER N 'Field Series', A.Name N' Field name ', (Case When ColumnProperty (A.ID, A.Name,' isidentity ') = 1 Ten' √'lse '' end) N 'Identifier', (Case WHEN (Select Count (*) from sysobjects where Name in (Select Name from sysindexes where (id = a.id) AND (INDID INDID) AND (ID = A.ID) and (Colid in (Select Colid from syscolumns where (id = a.id) (Name = a.name))))))))))))))))> 0 THEN '√' else '' end) n 'primary key', B.Name N 'type', a.length n 'Used bytes', ColumnProperty (A.ID, A.NAME,' Precision ') AS N' Length ', ISNULL (ColumnProperty (A.ID, A.NAME,' Scale '), 0) AS N' Dec Bit ', (Case When A.isnullable = 1 Then' √'ELSE '' end) N 'Allow empty', isnull (E.TEXT, '') n 'default', isnull (g. [Value], '') AS N 'Field Description' from syscolumns a left join systemypes b on a.xtype = B.xUserty Peinner Join Sysobjects D on A.ID = D.ID and D.XTYPE = 'u' and D.Name <> 'DTProperties'Left Join SysComments Eon A.cdefault = E.IDLEFT JOIN SYSPROPERTIES GON A.ID = G.ID And A.Colid = g.smallid order by Object_name (a.id), A.ColORDER2. Check Database Physical File Size - Script That Will Generate An Alert if We've Had FileGrowthuse Mastergo
--Error message. Procedure raises error 50001. Adjust to your needsEXEC sp_addmessage @msgnum = 50001, @severity = 12, @with_log = 'true', @msgtext = 'Filegrowth has occured to file% s in database% s.', @replace = 'REPLACE'GO - Create alert for errormessage 50001. Adjust to your needsUSE msdbEXEC sp_add_alert @name =' FileGrowth ', @message_id = 50001, @notification_message =' We''ve had file growt .'-- Specify operators . to alert Adjust operatorname to your environment.EXEC sp_add_notification 'FileGrowth', 'SQLAdmins', 1USE masterGO - Clean up so we can run several timesDROP TABLE master.dbo.dbmsys_dbfilesGODROP PROC sp_dbm_checkfilegrowthGO
--This is the procution ofcreate proc sp_dbm_checkfilegrowth as / ************************************************ **************************************************************** / / * THIS procedure writes an error message to eventlog if there has occured * // * growth for any file in any database. The procedure is designed to run as * // * a scheduled job regurarly. Adjust error number, error message and operator * // * IN Beginning of Script File and Error Number in Proc Code. * // * The procedure takes no parameters: * // * TESTED ON VERSION 7.0 and 8.0. * // ************ *********************************************************** **************** / SET NOCOUNT ON - GET VERSION NUMBER AND VERIFY Supported VersionDeclare @VER VARCHAR (7) SELECT @VER = Case When Charindex ('7.00', @@ Version )> 0 THEN '7.00' WHEN CHARINDEX ('8.00', @@ Version)> 0 Then '8.00' else 'unknown'end if @Ver =' Unknown'Begin Raiserror ('Uns Upported Version of SQL Server. ', 16, 1) Return -101nd - Declare Variables SectionDeclare @dbname sysname, @filename varchar (260), @cursize int, @Lastsize Int
--Verify that table to hold information exists. --If not, create and issue message that this is first execution and table was createdIF NOT EXISTS (SELECT * FROM master.dbo.sysobjects WHERE name = 'dbmsys_dbfiles') BEGIN CREATE TABLE master .dbo.dbmsys_dbfiles (dbname sysname NOT NULL, dbfilename sysname NOT NULL, mb_size INT NOT NULL CONSTRAINT pk_dbmsys_dbfiles PRIMARY KEY (dbname, dbfilename)) RAISERROR ( 'First execution of sp_dbm_checkfilegrowth. Table master.dbo.dbmsys_dbfile created.', 12, 1 Return End
--For each database name in sysdatabasesDECLARE db CURSOR FOR SELECT CATALOG_NAME FROM INFORMATION_SCHEMA.SCHEMATAOPEN dbFETCH NEXT FROM db INTO @dbnameWHILE @@ FETCH_STATUS = 0BEGIN --For each file in this database EXEC ( 'DECLARE dbfile CURSOR FOR SELECT RTRIM (filename), Size from ' @dbname ' ..sysfiles') Open dbfile fetch next from dbfile @filename, @cursize while @@ fetch_status = 0 begin --if we don't have the an entry for the file in Our Table, insert it IF NOT EXISTS (SELECT * FROM master.dbo.dbmsys_dbfiles WHERE dbfilename = @filename) INSERT master.dbo.dbmsys_dbfiles (dbname, dbfilename, mb_size) VALUES (@dbname, @filename, @cursize) ELSE BEGIN --Get file size last time we executed SELECT @lastsize = mb_size FROM master.dbo.dbmsys_dbfiles WHERE dbfilename = @filename --Did we have a filegrowth? IF @cursize> @lastsize RAISERROR (50001, -1, 1, @filename, @dbname) --Refresh sys_dbfiles to reflect current file size update master.dbo.dbmsys_dbfiles s ET mb_size = @cursize WHERE dbfilename = @filename AND dbname = @dbname END FETCH NEXT FROM dbfile INTO @filename, @cursize END CLOSE dbfile DEALLOCATE dbfile FETCH NEXT FROM db INTO @ dbnameEND - DELETE entries in sys_dbfiles for db's that no longer existsDELETE Master.dbo.dbmsys_dbfiles where dbname not in (select catalog_name from information_schema.schemata) Close DBDEAllocate DBGO
--Use EM to schedule execution of master..sys_checkfilegrowth with desired frequency3.Kill All Connecting ProcessUSE masterGOCREATE PROC sp_dbm_kill_users @dbname sysname, @delay DATETIME = '00: 00 'AS / ************ *********************************************************** **************** // * This Procedure Executes Kill for All Connections in the specified database. * // * The Procedure Can Be Mofified SO It Kills All Connections for a Server. * // * The procedure takes the folload parameters: * // * @dbname sysname (required): Database name. * // * @delay datetime (optional) [0]: Optional delay (s) before resume. * /////////////// * TESTED ON VERION 6.5, 7.0 and 8.0. * // ******************************************** ************************************************************** / SET NOCOUNT ON - GET VERSION Number and verify supported versionDeclare @Ver varchar (7) SELECT @VER = Case When Charindex ('6.50', @@ version)> 0 Then '6.5 0 'When Charindex (' 7.00 ', @@ Version)> 0 Then' 7.00 'When Charindex (' 8.00 ', @@ version)> 0 Then' 8.00 'else' unknown'endiff @Ver = 'Unknown'Begin Raiserror ('Unsupported version of SQL Server.', 16, 1) Return -101ndDeclare loop_name Innsitive Cursor for select spid from master..sysprocesses where dbid = db_id (@dbname)
OPEN loop_nameDECLARE @conn_id SMALLINTDECLARE @exec_str VARCHAR (255) FETCH NEXT FROM loop_name INTO @conn_idWHILE (@@ fetch_status = 0) BEGIN SELECT @exec_str = 'KILL' CONVERT (VARCHAR (7), @conn_id) EXEC (@exec_str) FETCH Next from loop_name @Conn_id enddeallocate loop_namewaitfor delay @DELAYGO
/ * Sample Execution: EXEC sp_dbm_kill_users pubsEXEC sp_dbm_kill_users @dbname = pubs, @delay = '00: 00:. 05 '* / 4 transmits the user information USE masterGOCREATE PROC sp_dbm_notify_users @msg VARCHAR (255) AS / ******* *********************************************************** *********************** * This Procedure Does a net send to all connected computers. * // * Requires That The Messenger Service is Running on The Client * // * Shortcut to the ba file, for instance * // * the procedure takes the following Parameter: * // * @msg varchar (255): The message to be self * // * TESTED ON VERSION 6.5, 7.0 and 8.0. * // ************ *********************************************************** ************************ / SET NOCOUNT ON - GET VERSION NUMBER AND VERIFY Supported VersionDeclare @VER VARCHAR (7) SELECT @VER = Case When Charindex ('6.50', @@ Version)> 0 Then '6.50' When Charindex ('7.00', @@ Version)> 0 TEN '7.00' When Charindex ('8.00', @@ version)> 0 THEN '8.00' ELSE 'Unknown'END IF @ver =' Unknown'BEGIN RAISERROR ( '. Unsupported version of SQL Server', 16,1) RETURN -101END - Declare variables sectionDECLARE loop_name INSENSITIVE CURSOR FOR SELECT DISTINCT LTRIM (RTRIM (Hostname)) from master..xysprocesses Where Datalength (Ltrim (RTRIM (Hostname))> 0
OPEN loop_nameDECLARE @host_name VARCHAR (30) DECLARE @exec_str VARCHAR (255) FETCH NEXT FROM loop_name INTO @host_nameWHILE (@@ fetch_status = 0) BEGIN SELECT @exec_str = 'master..xp_cmdshell "NET SEND' @host_name '' @MSG '"' EXEC (@exec_str) fetch next from loop_name @HOST_NAME Enddeallocate loop_namego
/ * Sample Execution: EXEC sp_dbm_notify_users 'SQL Server will shut down in 30 minutes!' * / 5 execute SQL statements Export to file USE masterGOCREATE PROC sp_dbm_query_to_file @db sysname, @query VARCHAR (255), @file VARCHAR (255). AS / ******************************************************************* ****************************************** * This Procedure Writes The Result from a Query to a file. * // * The procedure takes the following parameters: * // * @db SYSNAME (required):. the database where the query will be executed * // * @query VARCHAR (255) (required): the query to be executed * //. * @File (varchar (255): The filename. * // * TESTED ON VERSION 6.5, 7.0 and 8.0. * // ******************* *********************************************************** ********** / SET NOCOUNT ON - GET VERSION NUMBER AND VERIFY Supported VersionDeclare @VER VARCHAR (7) SELECT @VER = Case When Charindex ('6.50', @@ Version)> 0 Then ' 6.50 'When Charindex (' 7.00 ', @@ version)> 0 Then' 7.00 'When Charindex (' 8.00 ', @@ Version)> 0 Then' 8.00 'else' unknown'endiff @Ver = 'Unknown'Begin Raiserror ('Unsupported version of SQL Server.', 16, 1) RETURN-101ENDEXEC ('master..xp_cmdshell' 'isql / o' @file '/ d' @db '/ q "' @query '"/ E' '')
/ * Sample Execution: EXEC sp_dbm_query_to_file 'pubs', 'SELECT au_fname FROM authors', 'c: /result.txt'EXEC sp_dbm_query_to_file @db =' pubs ', @query =' SELECT au_fname FROM authors ', @file =' c : /Result.txt '* /
GO6. Check if the database physical file size exceeds the selected proportion USE MASTERGOCREATE PROC sp_dbm_warn_if_full_db @fillpercentage decimal (5, 2) = null as / **************************************************************************************************************************************************************************************************************** *********************************************************** . ******* // * Generates warning is db is fuller than supplied value * // * The procedure does not take logspace into account * // * The procedure takes the following parameter:. * // * @fillpercentage Decmal (5, 2) [NULL]: How Full The DB Should Be for a * // * Warning to Be generated. * // * by Specifying 1 AS Parameter, You Get a Message Each Execution. * // * You might Want to Run this as post-projecting job, so you get recalc. * // * this proc does not include Transaction log space in Calculation 6.5, 7.0 and 8.0. * // **** *********************************************************** ************************ / S ET NOCOUNT ON - GET VERSION NUMBER AND VERIFY Supported VersionDeclare @VER VARCHAR (7) SELECT @VER = Case When Charindex ('6.50', @@ Version)> 0 Then '6.50' When Charindex ('7.00', @@ Version )> 0 THEN '7.00' WHEN CHARINDEX ('8.00', @@ Version)> 0 Then '8.00' else 'unknown'Begin Raiserror (' unsupported version of SQL Server. ', 16, 1) RETURN-101END
--Declare variables sectionDECLARE @db_full_pages FLOATDECLARE @db_full_percentage FLOATDECLARE @msg VARCHAR (255) DECLARE @exec_str VARCHAR (255) DECLARE @db_size FLOAT - Below due to strange behavior for sysfiles table (does not act global as other system tables) CREATE TABLE #db_size (db_size FLOAT) IF @fillpercentage NOT BETWEEN 1 AND 99 OR @fillpercentage IS NULLBEGIN RAISERROR ( 'Bad parameters: @fillpercentage = How full db for warning to be generated.', 16, 1) RETURN -101END
IF @VER = '6.50'begin - user defined segments Are Not supported if (Select Count (*) from master..sysusages where segmap not in (3, 4, 7) and dbid = db_id ())> 0 Begin Raiserror ('User Defined Segments Are Not Supported.', 16, 1) Return -103 End
IF (Select Count (*) from master..sysusages where segmap = 7 and dbid = db_id ()> 0 - data and log not sedate begin select @db_size = sum (size) from master..sysusages where dbid = db_id () SELECT @ db_full_pages = SUM (Reserved) from sysindexes where indid in (0, 1) end else in (select count (*) from sysusages where segmap in (3, 4))> 0 - Data and log Separated Begin Select @db_size = SUM (size) FROM master..sysusages WHERE dbid = DB_ID () AND segmap = 3 SELECT @ db_full_pages = SUM (reserved) FROM sysindexes WHERE indid IN (0,1) AND id! = OBJECT_ID ( 'syslogs') ENDENDELSEBEGIN SELECT @exec_str = 'INSERT #db_size SELECT SUM (size) FROM' DB_NAME () '..sysfiles WHERE status & 0x40 <> 0x40' EXEC (@exec_str) SELECT @db_size = db_size FROM #db_size SELECT @db_full_pages = SUM (reserved) FROM sysindexes WHERE indid IN (0,1) ENDSELECT @db_full_percentage = @ db_full_pages / @ db_size * 100IF @db_full_percentage> @fillpercentage BEGIN SELECT @msg = 'Database' DB_NAME () 'Is' Convert (VARCHAR (10), Convert (Decimal (10, 0), @db_ffull_percentage)) '%% Full.' Raiserror (@msg, 15, 1) end
/ * Sample Execution: sp_dbm_warn_if_full_db 70 * /
Go
