itblog.team-holm.net

List all files in all databases in current instance:

SELECT name, physical_name AS current_file_location
FROM sys.master_files;

Find what database a given mdf file belongs to:

SELECT name FROM master..sysdatabases
WHERE filename LIKE '%your_mdf_file%';

Find the size of a given database:

SELECT DB_NAME(database_id) AS DatabaseName,
Name AS Logical_Name,
Physical_Name, (size*8)/1024 SizeMB
FROM sys.master_files
WHERE DB_NAME(database_id) = 'AdventureWorks'
GO

Find how long the current instance has been up:

SELECT login_time FROM sys.dm_exec_sessions
WHERE session_id = 1;

Find what database a given mdf file belongs to:

SELECT name FROM master..sysdatabases
WHERE filename LIKE '%your_mdf_file%';

Kill the most CPU intensive process:

SELECT * FROM sys.dm_exec_requests
ORDER BY cpu_time DESC;

The process on the top of the list has session_id 9,
thus we kill session_id 9.
KILL 9;

SQL 2008 How to reset lost SA account password

-19th of October 2013

Here is an easy to follow guide on how to reset a lost SA account password in Microsoft SQL Server:

   1. Stop SQL Service: on the command line type: net stop MSSQLServer
   2. Start the SQL Server in Management mode: on the command line type: net start MSSQLServer /m
   3. Open the SQL Server management studio, cancel the login dialog
   4. Open new sql server engine query window: from the menu, Click file->new->Database engine query
   5. Enable SA account if not enabled: in the query window type: Alter login sa enable
   6. Set the password of the sa account: alter login sa with password='my password'
   7. Stop the SQL server from the command line: net stop MSSQlServer
   8. Start SQL Service from the command line: net start mssqlserver
   9. Start the SQL Management studio and connect to the server using sa account
   10. Add you domain administrator as sysadmin
   11. Disable the sa account when you finish

Source: Useful stuff

Tagged as: SQL 2008, HowTo

					/****************************************************/
					/* Created by: SQL Server 2008 R2 Profiler          */
					/****************************************************/
					
					-- Create a Queue
					declare @rc int
					declare @TraceID int
					declare @maxfilesize bigint
					set @maxfilesize = 500

					-- Please replace the text InsertFileNameHere, with an appropriate
					-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
					-- will be appended to the filename automatically. If you are writing from
					-- remote server to local drive, please use UNC path and make sure server has
					-- write access to your network share

					exec @rc = sp_trace_create @TraceID output, 0, N'F:\MSSQL\InsertFileNameHere', @maxfilesize, NULL 
					if (@rc != 0) goto error

					-- Client side File and Table cannot be scripted

					-- Set the events
					declare @on bit
					set @on = 1
					exec sp_trace_setevent @TraceID, 10, 1, @on
					exec sp_trace_setevent @TraceID, 10, 3, @on
					exec sp_trace_setevent @TraceID, 10, 11, @on
					exec sp_trace_setevent @TraceID, 10, 35, @on
					exec sp_trace_setevent @TraceID, 10, 12, @on
					exec sp_trace_setevent @TraceID, 10, 13, @on
					exec sp_trace_setevent @TraceID, 45, 1, @on
					exec sp_trace_setevent @TraceID, 45, 3, @on
					exec sp_trace_setevent @TraceID, 45, 11, @on
					exec sp_trace_setevent @TraceID, 45, 35, @on
					exec sp_trace_setevent @TraceID, 45, 12, @on
					exec sp_trace_setevent @TraceID, 45, 28, @on
					exec sp_trace_setevent @TraceID, 45, 13, @on
					exec sp_trace_setevent @TraceID, 12, 1, @on
					exec sp_trace_setevent @TraceID, 12, 3, @on
					exec sp_trace_setevent @TraceID, 12, 11, @on
					exec sp_trace_setevent @TraceID, 12, 35, @on
					exec sp_trace_setevent @TraceID, 12, 12, @on
					exec sp_trace_setevent @TraceID, 12, 13, @on


					-- Set the Filters
					declare @intfilter int
					declare @bigintfilter bigint

					set @bigintfilter = 50000
					exec sp_trace_setfilter @TraceID, 13, 0, 4, @bigintfilter

					exec sp_trace_setfilter @TraceID, 35, 0, 6, N'tmsng'
					-- Set the trace status to start
					exec sp_trace_setstatus @TraceID, 1

					-- display trace id for future references
					select TraceID=@TraceID
					goto finish

					error: 
					select ErrorCode=@rc

					finish: 
					go

					-- To stop the trace, here with Id 2:    
					exec sp_trace_setstatus 2, 0     
					-- To find traceID if is unknown:
					-- SELECT traceid FROM fn_trace_getinfo(default) 
					--				where traceid > 1

 
				-- To deleta a trace race:
				exec sp_trace_setstatus 2, 2     
				-- To delete the trace you first have to stop it. 
				-- This will close the trace file being written to.

				-- As an alternative to the above commands the 
				-- 		following code will also work:
				declare @tracenr int
				SELECT @tracenr = MAX(traceid) 
					FROM fn_trace_getinfo(default) where traceid > 1
				exec sp_trace_setstatus @tracenr, 0
				exec sp_trace_setstatus @tracenr, 2

Firstly we need start our trace and store the results in a file that we can analyze later. Use the script on the left hand side created by SQL Server 2008 R2 Profiler and change the path and filename in 'InsertFileNameHere' to a real path on your system. You can run it in SQL Server Management Studio, this will start the trace that collects the data we need. See comments in the script for further instructions.

Let the trace run for a day or two, or at least a few hours where you know the system is under strain. To stop the trace use the stored procedure shown in the first box at the top of this column on the right hand side. The trace Id was returned to you when you started the trace, but if you have forgotten or missed it, you will find a query to find it for you in the same box at the top of this column.

When you are all done, it might be a good idea to clean up after yourself and delete the trace you created from the system. You will find commands to do that in the second box fron the top in this column. Alternatively you can use the code in the third box from the top of this column to make your day.

Now that you have your data collected in the trace file you created, you can start Database Engine Tuning Advisor and load the trace in there for analysis. Specify the file name and the database you want to evaluate as shown in figure 1, and start the analysis with the "Start Analysis" button.

Figure 1:

This blog article will be updated later with more information on Database Engine Tuning Advisor and how to use it with what you have seen so far. In the meanwhile, you are off to a start with this small guide. The next steps should be intuitive. For a closer look take a look at Ken Lassesen's guide and jump down to the "Apply the Recommendations" section. You have already performed all the rest above that section when reading and following this guide, thus simplifying the process of doing this greatly.