Read the trace file from SSMS

Considering am having a trace file in the location,

"C:\Program Files\Microsoft SQL Server\MSSQL10.SQLSERVER2008\MSSQL\DATA\audittrace20100707161605.trc" How to open the trace file? Is it possible to fetch it as a table. So that, we can play around the data available in the trace.

Option 1 - We can see the trace file using SQL Profiler.

Option 2: Use the below query to read the data in SSMS

SELECT *
FROM ::fn_trace_gettable(
'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLSERVER2008\MSSQL\DATA\audittrace20100707161605.trc', default
)
GO

::fn_trace_gettable -> this function will read the trace file and the output is a table.

Cheers,

Venkatesan Prabu .J

Audit error in SQL Server 2008

While creating audit and enabling it, am getting the below error.

------------------------------------------------------------------------------------
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Cannot alter a server audit from a user database. This operation must be performed in the master database. (Microsoft SQL Server, Error: 33074)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.00.1600&EvtSrc=MSSQLServer&EvtID=33074&LinkId=20476
------------------------------BUTTONS:
OK------------------------------
------------------------------------------------------------------------------------

Microsoft have accepted it as a bug in the product and it will be fixed in future versions. Below is the work around,

1. Execute the below query
TSQL:
USE [master]
GO
ALTER SERVER AUDIT [YouAuditName] WITH (STATE=ON)
GO

2. Change the default database for the logged in user as master and after wards enable the Audit.

Cheers,
Venkatesan Prabu .J

Get autoclose and autoshrink values for the databases in sql server

AutoClose Option for SQL Server:

By default, this option should be false. If the connection to the database or the last user release his connection from the database. Immediately, the database will think that all the connections were closed. So it will get closed and shutdown automatically. In addition, it will release all the memory and other resources.
This will impact the performance of the application due to database shutdown if there is no connections. Releasing the resources and getting the resources back while the database is online will hurt the performance. So, it's advisable to keep this option false for ever in the production systems.

Auto Shrink:

It's an other option which needs to be kept off for the production databases. This option will shrunk the database files automatically if the free space of the database files is more than 25 percent. If the shrinking of database files happened it will surely affect the data position + your indexes will get affected hugely. That's why, this option needs to be made false.

Below is the query to find the options set in your databases.


SELECT [name] AS DatabaseName
, CONVERT(varchar(10),DATABASEPROPERTYEX([Name] , 'IsAutoClose')) AS AutoClose
, CONVERT(varchar(10),DATABASEPROPERTYEX([Name] , 'IsAutoShrink')) AS AutoShrink
FROM master.dbo.sysdatabases
Order By DatabaseName






Cheers,
Venkatesan Prabu .J

My SQL Server session in Chennai

Check my SQL Server 2008 session in Chennai on July 17th.

It's a biggest SQL Server MVP Summit in chennai. I am taking a session on SQL Server 2008 - Manageability features. Hoping to meet you all (My dear Blog readers)

URL - http://sql-articles.com/events/

Cheers,
Venkatesan Prabu .J

Language ID in SQL Server

Usually, we used to here the messages like problem with 1033 folder or similar kind of problem. What's this 1033 folder?

It's nothing but "A specification for the SQL Server language installed in the machine"

1033 - In windows term language, it's english language. You can get the language supported by a particular version or installation in SQL Server by executing the below query,

select * from syslanguages

Cheers,
Venkatesan Prabu .J

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding in windows appilcation

While trying to use windows application, I got a peculiar timout error.

SqlCommand cmd = new SqlCommand("My_Report_Proc", conn);
cmd.CommandType = CommandType.StoredProcedure;

//dataGridView1.DataSource = cmd.ExecuteReader();
// bind the data source to the datagrid
//dataGridView1.databind();
// close the connection to the database since we are done using it
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds); --Got the error at this point
dataGridView1.DataSource = ds.Tables[0];

Error string:

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding in windows appilcation

I have tried adding connection timout (Connect Timeout=500;). Still, the problem is not resolved. After wards, I have added the command cmd.CommandTimeout = 0; This issue is resolved.


conn.Open();
SqlCommand cmd = new SqlCommand("My_Report_Proc", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = 0;
//dataGridView1.DataSource = cmd.ExecuteReader();
// bind the data source to the datagrid
//dataGridView1.databind();
// close the connection to the database since we are done using it
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
dataGridView1.DataSource = ds.Tables[0];
//GridView.DataBind()


Cheers,
Venkatesan Prabu .J