Setting max file size to current physical size

Scenario: On SQL 2008R2 server, Tempdb has unlimited growth max size and is in danger of filling disk.  

Need a quick way to set max size to current size to prevent further growth until can assess if there is an abnormal process taking up TempDB, what optimal size of TempDB should be, and/or disk space can be added.

SELECT 'ALTER DATABASE [' + db_name(A.database_id) 
     + '] MODIFY FILE ( NAME = N''' + A.name 
     + ''', MAXSIZE = ' 
     + cast((B.size_on_disk_bytes/1024) as varchar) + ' KB)'
     , A.*
     , B.*
  FROM master.sys.master_files A
  JOIN sys.dm_io_virtual_file_stats( db_id('tempdb'),NULL) B
    ON A.file_id = B.file_id
 WHERE A.database_id = db_id('tempdb')

ASYNC_NETWORK_IO

High ASYNC_NETWORK_IO often caused by MS Access or MS Excel connections

SQL Service Aliases

SQL Service Aliases (example instance name INST01)

NT SERVICE\MSSQL$INST01
NT SERVICE\SQLAgent$INST01
NT SERVICE\MSOLAP$INST01

Who locked my files?

Find out who has shared folder files locked and even force close them.

Use Computer Management snap-in, navigate to Shared Folders and Open Files

How to transfer logins and passwords between instances of SQL Server

This article describes how to transfer the logins and the passwords between instances of Microsoft SQL Server 2005, of Microsoft SQL Server 2008, and of Microsoft SQL Server 2012 on different servers.

The script in this article creates two stored procedures in the master database: sp_hexadecimal and sp_help_revlogin.

https://support.microsoft.com/en-us/kb/918992


Having the SIDs of sql logins match across multiple instances is especially useful in various scenarios including:

1.  Where the production database is frequently restored onto development and test instances, no more needing to run sp_change_users_login after every refresh

2. Mirrored and High Availability Group databases so that sql logins on the secondary instance match the users in the database after a failover.

Troubleshooting Checklist

When I get a call about poor performance, these are the things I check first.  What do you check for?  Leave comments below...

Moving SQL Agent output file

If you move the location of the SQL Agent output file by changing the parameter in SQL Server Configuration Manager, you may also need to update the registry entry.

Check the value of key ErrorLogFile in the following registry hive:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\SQLServerAgent

Check the path in the ErrorLogFile entry