Error handler: TRY CATCH

BEGIN TRANSACTION TRAN1

BEGIN TRY

INSERT INTO.....

UPDATE.....

END TRY

BEGIN CATCH

If @@TRANCOUNT > 0

--error happened

ROLLBACK TRANSACTION TRAN1

END CATCH

If @@TRANCOUNT > 0

COMMIT TRANSACTION TRAN1

GO

Script: Log file size to a user table

DECLARE @rowcnt INT
DECLARE @iterator INT
DECLARE @dbname VARCHAR(200)
DECLARE @exec_sql VARCHAR(500)
SET @rowcnt = 0
SET @iterator = 1
CREATE TABLE #db_file_info ( [Database_Name] SYSNAME NOT NULL, [File_ID] SMALLINT NOT NULL, [File_Type] VARCHAR(10) NOT NULL, [File_Name] SYSNAME NOT NULL, [File_Path] VARCHAR(500) NOT NULL, [File_Size_in_MB] INT NOT NULL, [Space_Used_in_MB] INT NOT NULL, [Space_Left_in_MB] INT NOT NULL, [Time_collected] smalldatetime )
CREATE TABLE #db ( dbid INT, name VARCHAR(200))
INSERT INTO #db
SELECT database_id, name FROM sys.databases
WHERE --database_id <> 11 AND [state] <> 6
SET @rowcnt = @@ROWCOUNT
WHILE @iterator <= @rowcnt
BEGIN
SELECT @dbname = '['+ name + ']' FROM #db
SET @exec_sql = ' USE ' + @dbname + ';
Insert into #DB_FILE_INFO Select db_name(),fileid,case when groupid = 0 then ''log file'' else ''data file'' end, name,filename, [file_size] = convert(int,round ((sysfiles.size*1.000)/128.000,0)), [space_used] = convert(int,round(fileproperty(sysfiles.name,''SpaceUsed'')/128.000,0)), [space_left] = convert(int,round((sysfiles.size-fileproperty(sysfiles.name,''SpaceUsed''))/128.000,0)), getdate() from dbo.sysfiles; '
EXEC( @exec_sql)
SET @iterator = @iterator + 1
END
INSERT INTO SQLAdmin_Monitor_db_file_info
SELECT * FROM #db_file_info
DROP TABLE #db
DROP TABLE #db_file_info

Modifying database file

All the database files are set to autogrowth to 10% from the initial sizes. But,the auto growth can happen any time during business hours causing the database slow down. Therefore I am taking the following approach -

1 I have set up a sql job ( calls a script listed here) that logs the current datafile and the log file size in a table in interval of 4 hours daily. I use that historical data to research when the data file needs to grow.
2. Then I run this script from the table to find which database file has more than 95% filled up and those are the candidate for me to manually resize ( expand) the data file.
3. I use the follwing script to resize the datafile
use db1
Go

---select * from sys.sysfiles

alter database client_343
modify file
(
name = client_343,
size = 1024MB
);

Script: To find the database file where the file space is used more than 94 % of the current file size

--The historic data is logged in a table in every 4 hours and the following query in run in the table to find the current grown rate of the databse file

SELECT Database_Name,Space_Used_in_MB,File_Size_in_MB,(cast(Space_Used_in_MB as float)/cast(File_Size_in_MB as float)) * 100 as newval
FROM Monitor_db..SQLAdmin_Monitor_db_file_info
WHERE FILE_ID = 1 AND (cast(Space_Used_in_MB as float)/cast(File_Size_in_MB as float)) * 100 >= 95 AND Time_collected IN (SELECT MAX(time_collected) FROM hCFS_SQLMonitor..SQLAdmin_Monitor_db_file_info )
ORDER BY File_Size_in_MB

restore msdb database

USE master
GO
--just to verify if the below command succeeds the restore should succeed too
RESTORE VERIFYONLY
FROM DISK = N'V:\SANSQL01Backups\Daily\msdb\msdb_backup_2014_10_05_194001_4668459.bak'
--restore the database
RESTORE DATABASE [msdb]
FROM DISK = N'V:\SANSQL01Backups\Daily\msdb\msdb_backup_2014_10_05_194001_4668459.bak'
WITH REPLACE
GO
--msdb database was curripted and when to suspect mode. So fixed it by restoring a copy of a backup

Removal of unused database to free up space.

I am trying to free up some space in the server by detaching the unused database and deleting the ldf & mdf files. Here are the steps I took
1. Performed a full back up of the database
2. Moved the BAK file to a different location in another server
3. Ran the follwing on the database since it was a publisher in the replication
sp_replicationdboption 'dbcentral,'publish','false'
4.Detach the database
5. Restore the db from the bakupfile somewhere to make sure that the back up is good.
6. Delete the files since I have a full back up.

tempdb modify size, move file

Modify the size
ALTER DATABASE tempdb MODIFY FILE ( NAME = tempdev, SIZE = 1024 ).
The database will be resized when the server restarts next.
Move the file
ALTER DATABASE tempdb MODIFY FILE ( NAME = tempdev, FILENAME = 'x:\full\path\xxxx\tempdb\file\tempdb.mdf' /*, SIZE = .... */ ) the file will be created in the new path when the server starts next