We often like to check the progess or completed percentage of time consuming command. You can query the sys.dm_exec_requests DMV to check the status of the command.
You must have atleast view state permission to execute the below query.
ALTER INDEX REORGANIZE
AUTO_SHRINK option with ALTER DATABASE
BACKUP DATABASE
DBCC CHECKDB
DBCC CHECKFILEGROUP
DBCC CHECKTABLE
DBCC INDEXDEFRAG
DBCC SHRINKDATABASE
DBCC SHRINKFILE
RECOVERY
RESTORE DATABASE
ROLLBACK
TDE ENCRYPTION
[sourcecode language="sql"]
SELECT dmr.session_id,
dmr.status,
dmr.start_time,
dmr.command,
dmt.TEXT,
dmr.percent_complete
FROM sys.dm_exec_requests dmr CROSS APPLY sys.Dm_exec_sql_text(dmr.sql_handle) dmt WHERE dmr.command IN (‘ALTER’, ‘Backup’, ‘Restore’, ‘DBCC’, ‘Rollback’, ‘TDE’)
[/sourcecode]
Tuesday, March 29, 2011
Monday, March 28, 2011
How to Check When SQL Server was installed?
NT AUTHORITY\SYSTEM login gets created when we install SQL Server. By querying sys.syslogins, we would get an idea of the SQL Server Installation date time as per the creation date time of NT AUTHORITY\SYSTEM.
SELECT createdate AS sql_server_install_date FROM sys.syslogins WHERE sid = 0x010100000000000512000000
SELECT * FROM sys.syslogins WHERE sid = 0x010100000000000512000000
[sourcecode language="sql"]
SELECT createdate AS sql_server_install_date FROM sys.syslogins WHERE sid = 0x010100000000000512000000
SELECT * FROM sys.syslogins WHERE sid = 0x010100000000000512000000
[/sourcecode]
SELECT createdate AS sql_server_install_date FROM sys.syslogins WHERE sid = 0x010100000000000512000000
SELECT * FROM sys.syslogins WHERE sid = 0x010100000000000512000000
[sourcecode language="sql"]
SELECT createdate AS sql_server_install_date FROM sys.syslogins WHERE sid = 0x010100000000000512000000
SELECT * FROM sys.syslogins WHERE sid = 0x010100000000000512000000
[/sourcecode]
Dos Command to Check System Properties and Server Reboot time
Many times we would like to check OS information and boot time. We can check the operating system properties and reboot time by executing systeminfo command from dos prompt.
C:\>systeminfo
C:\>systeminfo
Saturday, March 26, 2011
Script to Update Statistics by passing database name
You can use below script to update the statistics with the FULL Scan. You can pass the database name in below script, I have given JShah as database name.
[sourcecode language="sql"]
EXEC Sp_msforeachdb
@command1=‘IF ”#” IN (”JShah”) BEGIN PRINT ”#”;
EXEC #.dbo.sp_msForEachTable ”UPDATE STATISTICS ? WITH FULLSCAN”, @command2=”PRINT CONVERT(VARCHAR, GETDATE(), 9) + ”” - ? Stats Updated””” END’
,@replaceChar = ‘#’
[/sourcecode]
You can use below script to update the statistics with the SAMPLE Percent agrument. You can pass the database name in below script, I have given JShah as database name.
[sourcecode language="sql"]
EXEC Sp_msforeachdb
@command1=‘IF ”#” IN (”JShah”) BEGIN PRINT ”#”;
EXEC #.dbo.sp_msForEachTable ”UPDATE STATISTICS ? WITH SAMPLE 50 PERCENT”, @command2=”PRINT CONVERT(VARCHAR, GETDATE(), 9) + ”” - ? Stats Updated””” END’
,@replaceChar = ‘#’
[/sourcecode]
[sourcecode language="sql"]
EXEC Sp_msforeachdb
@command1=‘IF ”#” IN (”JShah”) BEGIN PRINT ”#”;
EXEC #.dbo.sp_msForEachTable ”UPDATE STATISTICS ? WITH FULLSCAN”, @command2=”PRINT CONVERT(VARCHAR, GETDATE(), 9) + ”” - ? Stats Updated””” END’
,@replaceChar = ‘#’
[/sourcecode]
You can use below script to update the statistics with the SAMPLE Percent agrument. You can pass the database name in below script, I have given JShah as database name.
[sourcecode language="sql"]
EXEC Sp_msforeachdb
@command1=‘IF ”#” IN (”JShah”) BEGIN PRINT ”#”;
EXEC #.dbo.sp_msForEachTable ”UPDATE STATISTICS ? WITH SAMPLE 50 PERCENT”, @command2=”PRINT CONVERT(VARCHAR, GETDATE(), 9) + ”” - ? Stats Updated””” END’
,@replaceChar = ‘#’
[/sourcecode]
Monday, March 21, 2011
Resolving could not open a connection to SQL Server errors
Problem
Sometimes you may have issues connecting to SQL Server and you may get messages such as the following:
ERROR: (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) (Microsoft SQL Server, Error:) An error has occurred while establishing a connection to the server. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 5)
Or
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 1326)
Or
A network-related error or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 – No such host is known.) (Microsoft SQL Server, Error: 11001)
http://www.mssqltips.com/tip.asp?tip=2340
Click Me for Solution
Friday, March 18, 2011
Msg 15009, Level 16, State 1, Procedure sp_help, sp_helptext or object_definition()
Whenever user has in-sufficient permission and he is executing sp_help, sp_helptext or the object_definition function, user will get the below error message. This error occurs because user doesn’t have have permissions to the see the object metadata.
EXEC sp_helptext sp_indexdefrag
Msg 15009, Level 16, State 1, Procedure sp_helptext
To fix the issue we have to grant the VIEW DEFINITION permission to user.
We can turn on the View Definition permission on all databases for all the users having public role using below code.
USE master
GO
GRANT VIEW ANY DEFINITION TO PUBLIC
We can turn on the View Definition permission on all databases for the user Jugal using below query.
USE master
GO
GRANT VIEW ANY DEFINITION TO Jugal
We can turn on the View Definition permission on SQLDBPOOL for the user Jugal using below query.
USE SQLDBPOOL
GO
GRANT VIEW DEFINITION TO Jugal
EXEC sp_helptext sp_indexdefrag
Msg 15009, Level 16, State 1, Procedure sp_helptext
To fix the issue we have to grant the VIEW DEFINITION permission to user.
We can turn on the View Definition permission on all databases for all the users having public role using below code.
USE master
GO
GRANT VIEW ANY DEFINITION TO PUBLIC
We can turn on the View Definition permission on all databases for the user Jugal using below query.
USE master
GO
GRANT VIEW ANY DEFINITION TO Jugal
We can turn on the View Definition permission on SQLDBPOOL for the user Jugal using below query.
USE SQLDBPOOL
GO
GRANT VIEW DEFINITION TO Jugal
Tuesday, March 15, 2011
Replication Error Message Number and Description
Check out http://msdn.microsoft.com/en-us/library/cc645609.aspx for replication error message.
Monday, March 14, 2011
Doing a lot of SQL coding - Try SQL Complete
Are you doing a lot of SQL coding every day and looking devouringly at every tool offering replacement of the native SSMS IntelliSense available only for SQL Server 2008? Then don't miss Devart dbForge SQL Complete while you are seeking an assistant to do the boring part of your work that should have been automated and simplified long ago.
General Information
The tool is available in two editions:
Express - a free edition providing basic functionality to complete and format SQL code.
Standard - fully-featured edition providing all necessary functionality for completing and formatting T-SQL code.
30-day trial period available for Standard Edition should be enough for anyone to test all product features and check if it meets one's requirements.
During evaluation and usage, you can submit requests and suggestions to the tool's support team. Besides, you can take part in creating its development roadmap at UserVoice.
I chose to install trial of SQL Complete Standard Edition. The installation took several seconds, and the tool and all its options are ready to use and easy to access from the SQL Server Management Studio main menu:
OK, but we will peep into the options later, when we see how the tool actually works and be sure that it's worth spending time on learning different advanced settings. Now we want to type any query we think of first. This can be as simple as SELECT * FROM:
What can we see? The tool filtered available suggestions depending on the probability of their usage. This saved me from tedious selecting of the needed word in a list sorted alphabetically (imagine it was the last one in it!) or typing almost the whole word
Now I'd like to exclude some columns from the table I am working with in this statement. Unfortunately, the “*” symbol is rarely replaced with the columns list by code author. But I found a way to do this quickly and effortlessly using the tool – just pressed Tab, as was written in the hint, and it was all:
Surely, you often have to join tables in queries just as I do. Quite simply, these statements allow combining data in multiple tables to quickly and efficiently process large quantities of data, but often they take too much time to write. There is a feature declaring SQL Complete capability to do such a trick painlessly for the one who is writing code:
I can say these were positive impressions, and the tool is really worth spending more time for testing. I decided on using some more complicated unformatted query, as the vendor puts emphasis on the tool's advanced formatting capabilities. Here's what we've got:
Before:
After:
My query was successfully formatted and is readable now.
Being able to access essential information on a database object is pretty useful and saves some efforts on looking it up:
The tool offers quite a lot of formatting options and a wizard for importing and exporting settings – this should be useful for large companies where some standardized T-SQL code formatting is necessary:
SQL Complete can be used by professionals, amateurs, and everybody who has something to do with writing SQL code. Besides, the price of the fully-featured edition (less than $50.00), availability of the free edition, effective product support provided by its development team make it worth testing seriously when choosing a tool from a number of alternatives offering similar functionality.
General Information
The tool is available in two editions:
Express - a free edition providing basic functionality to complete and format SQL code.
Standard - fully-featured edition providing all necessary functionality for completing and formatting T-SQL code.
30-day trial period available for Standard Edition should be enough for anyone to test all product features and check if it meets one's requirements.
During evaluation and usage, you can submit requests and suggestions to the tool's support team. Besides, you can take part in creating its development roadmap at UserVoice.
Features
- Context-based prompt of different object types
- Context-based prompt of keywords
- Context-based prompt of function parameters
- Word autocompletion
- Automatic filtering of object in the suggestion list
- Context-based sorting of suggestions in the list
- Determining a current database or schema
- Supporting queries to various databases
- Automatic displaying suggestions while typing
- Two ways to insert a suggested word into a query
- Usage of syntax highlight settings for the suggestions list
- Query formatting
- Support of various query types
- Semi-transparent view of the suggestion box
- Inserting columns list on pressing Tab
- Suggesting methods for columns and variables
- Suggesting conditions for JOIN statement
- Automatic alias generation in SELECT statements
- Sorting keywords by relevance
- Quick object info
- Expanding INSERT statements
- Export/Import settings wizard
Review
I chose to install trial of SQL Complete Standard Edition. The installation took several seconds, and the tool and all its options are ready to use and easy to access from the SQL Server Management Studio main menu:
OK, but we will peep into the options later, when we see how the tool actually works and be sure that it's worth spending time on learning different advanced settings. Now we want to type any query we think of first. This can be as simple as SELECT * FROM:
What can we see? The tool filtered available suggestions depending on the probability of their usage. This saved me from tedious selecting of the needed word in a list sorted alphabetically (imagine it was the last one in it!) or typing almost the whole word
Now I'd like to exclude some columns from the table I am working with in this statement. Unfortunately, the “*” symbol is rarely replaced with the columns list by code author. But I found a way to do this quickly and effortlessly using the tool – just pressed Tab, as was written in the hint, and it was all:
Surely, you often have to join tables in queries just as I do. Quite simply, these statements allow combining data in multiple tables to quickly and efficiently process large quantities of data, but often they take too much time to write. There is a feature declaring SQL Complete capability to do such a trick painlessly for the one who is writing code:
I can say these were positive impressions, and the tool is really worth spending more time for testing. I decided on using some more complicated unformatted query, as the vendor puts emphasis on the tool's advanced formatting capabilities. Here's what we've got:
Before:
After:
My query was successfully formatted and is readable now.
Being able to access essential information on a database object is pretty useful and saves some efforts on looking it up:
The tool offers quite a lot of formatting options and a wizard for importing and exporting settings – this should be useful for large companies where some standardized T-SQL code formatting is necessary:
Summary
SQL Complete can be used by professionals, amateurs, and everybody who has something to do with writing SQL code. Besides, the price of the fully-featured edition (less than $50.00), availability of the free edition, effective product support provided by its development team make it worth testing seriously when choosing a tool from a number of alternatives offering similar functionality.
Friday, March 11, 2011
Steps to change default value of SELECT (Top 1000) or EDIT(Top 200) rows in SSMS 2008
As we all aware about the new feature of SQL server 2008, by right clicking any table we can see Select Top 1000 Rows and Edit Top 200 Rows. This feature will give us flexibility to SELECT 1000 rows and EDIT 200 rows just by right click.
Steps to change top Rows:
Step1 : Go to Tools -- Options --> SQL Server Object Explorer --> Commands
Table and View Options:
Value for Edit Top Rows command specifies number of columns to be edited. Default value is 200.
Value for Select Top Rows command specifies number of columns to be selected. Default value is 1000.
Step 2: Change Value for Edit Top Rows Command and Value for Select Rows Command per you requirement.
Step 3: Verify above changes have been made by again right clicking the table.
Steps to change top Rows:
Step1 : Go to Tools -- Options --> SQL Server Object Explorer --> Commands
Table and View Options:
Value for Edit Top Rows command specifies number of columns to be edited. Default value is 200.
Value for Select Top Rows command specifies number of columns to be selected. Default value is 1000.
Step 2: Change Value for Edit Top Rows Command and Value for Select Rows Command per you requirement.
Step 3: Verify above changes have been made by again right clicking the table.
Wednesday, March 9, 2011
sys.dm_clr_properties
sys.dm_clr_properties: Returns a row for each property related to SQL Server common language runtime (CLR) integration, including the version and state of the hosted CLR.
Enabling CLR using SP_Configure doesn't gurantee that it is initialized. To initialize CLR, you have to execute ASSEMBLY statements, CLR Routine Type or Trigger.
See below image for the output of this DMV.
Below are the different state of CLR.
Check http://msdn.microsoft.com/en-us/library/ms174983.aspx link for different CLR state description.
Enabling CLR using SP_Configure doesn't gurantee that it is initialized. To initialize CLR, you have to execute ASSEMBLY statements, CLR Routine Type or Trigger.
See below image for the output of this DMV.
Below are the different state of CLR.
- Mscoree is not loaded.
- Mscoree is loaded.
- Locked CLR version with mscoree.
- CLR is initialized.
- CLR initialization permanently failed.
- CLR is stopped
Check http://msdn.microsoft.com/en-us/library/ms174983.aspx link for different CLR state description.
Sunday, March 6, 2011
Steps to clear the server list in SQL Server Management Studio
Check out my new article
Steps to clear the server list in SQL Server Management Studio on MSSQLTIPS.com.
http://mssqltips.com/tip.asp?tip=2319
Thursday, March 3, 2011
SQL Server 2011 AKA Denali Step By Step Installation
SQL Server 2011 AKA Denali
As we all aware about that, Microsoft has released SQL Server 2011 CTP1. CTP stands for Community Technology Preview and it is a beta version of upcoming release.
Minimum Software and Hardware Requirement
SQL Server 2011 AKA Denali Installation Steps
[caption id="attachment_1115" align="aligncenter" width="614" caption="Step 1 : Go to Setup folder and click on Setup.exe"]
[/caption]
[caption id="attachment_1116" align="aligncenter" width="614" caption="Step 2: Go to Installation Page and Select Stand Alone Installation"]
[/caption]
[caption id="attachment_1117" align="aligncenter" width="614" caption="Step 3: Setup will validate the the OS for minimut requirement. You must have noReboot Package installed."]
[/caption]
[caption id="attachment_1118" align="aligncenter" width="614" caption="Step 4: Setup will again the check the rules for example Power Shell, Firewall, .Net Framework Security etc"]
[/caption]
[caption id="attachment_1119" align="aligncenter" width="614" caption="Step 5: Give you an option of new installation or add features to existing instance."]
[/caption]
[caption id="attachment_1120" align="aligncenter" width="614" caption="Step 6: Give you an option to Select edition"]
[/caption]
[caption id="attachment_1121" align="aligncenter" width="614" caption="Step 7: Accept Agreement Screen"]
[/caption]
[caption id="attachment_1122" align="aligncenter" width="614" caption="Step 8: Give you an option to select required feature"]
[/caption]
[caption id="attachment_1123" align="aligncenter" width="614" caption="Step 9: Give you an option to select the instance level features"]
[/caption]
[caption id="attachment_1124" align="aligncenter" width="614" caption="Step 10: Again validate the server for the selected features"]
[/caption]
[caption id="attachment_1125" align="aligncenter" width="614" caption="Step 11: Gives you an option to select the named instance/default instance"]
[/caption]
[caption id="attachment_1126" align="aligncenter" width="614" caption="Step 12: Will show your the disk space required for the selected features"]
[/caption]
[caption id="attachment_1127" align="aligncenter" width="614" caption="Step 13: Specify the service accounts"]
[/caption]
[caption id="attachment_1128" align="aligncenter" width="614" caption="Step 14: Specify the collation"]
[/caption]
[caption id="attachment_1129" align="aligncenter" width="614" caption="Step 15: Specify the Authentication Mode, Data Directories and File Stream"]
[/caption]
[caption id="attachment_1130" align="aligncenter" width="614" caption="Step 16: Data Directories Configuration"]
[/caption]
[caption id="attachment_1133" align="aligncenter" width="614" caption="Step 17: Click on Install and Installation will proceed."]
[/caption]
As we all aware about that, Microsoft has released SQL Server 2011 CTP1. CTP stands for Community Technology Preview and it is a beta version of upcoming release.
Minimum Software and Hardware Requirement
- We can install Denali on both the file system formats (FAT32 and NTFS) but the Microsoft recommends NTFS file format.
- Your Operating system must have "no-reboot" package installed prior to installing SQL Server 2011. By default it is included in Windows 7 SP1 and Windows Server 2008 R2. For the other OS you can download No Reboot package from http://support.microsoft.com/kb/982526 link.
- .NET Framework 4.0
- Virtualization is supported using Microsoft's Hyper-V technology.
- At least 3.4GB free disk space
- At least 2GB RAM
- Windows PowerShell 2.0 is a pre-requisite
SQL Server 2011 AKA Denali Installation Steps
[caption id="attachment_1115" align="aligncenter" width="614" caption="Step 1 : Go to Setup folder and click on Setup.exe"]
[/caption][caption id="attachment_1116" align="aligncenter" width="614" caption="Step 2: Go to Installation Page and Select Stand Alone Installation"]
[/caption][caption id="attachment_1117" align="aligncenter" width="614" caption="Step 3: Setup will validate the the OS for minimut requirement. You must have noReboot Package installed."]
[/caption][caption id="attachment_1118" align="aligncenter" width="614" caption="Step 4: Setup will again the check the rules for example Power Shell, Firewall, .Net Framework Security etc"]
[/caption][caption id="attachment_1119" align="aligncenter" width="614" caption="Step 5: Give you an option of new installation or add features to existing instance."]
[/caption][caption id="attachment_1120" align="aligncenter" width="614" caption="Step 6: Give you an option to Select edition"]
[/caption][caption id="attachment_1121" align="aligncenter" width="614" caption="Step 7: Accept Agreement Screen"]
[/caption][caption id="attachment_1122" align="aligncenter" width="614" caption="Step 8: Give you an option to select required feature"]
[/caption][caption id="attachment_1123" align="aligncenter" width="614" caption="Step 9: Give you an option to select the instance level features"]
[/caption][caption id="attachment_1124" align="aligncenter" width="614" caption="Step 10: Again validate the server for the selected features"]
[/caption][caption id="attachment_1125" align="aligncenter" width="614" caption="Step 11: Gives you an option to select the named instance/default instance"]
[/caption][caption id="attachment_1126" align="aligncenter" width="614" caption="Step 12: Will show your the disk space required for the selected features"]
[/caption][caption id="attachment_1127" align="aligncenter" width="614" caption="Step 13: Specify the service accounts"]
[/caption][caption id="attachment_1128" align="aligncenter" width="614" caption="Step 14: Specify the collation"]
[/caption][caption id="attachment_1129" align="aligncenter" width="614" caption="Step 15: Specify the Authentication Mode, Data Directories and File Stream"]
[/caption][caption id="attachment_1130" align="aligncenter" width="614" caption="Step 16: Data Directories Configuration"]
[/caption][caption id="attachment_1133" align="aligncenter" width="614" caption="Step 17: Click on Install and Installation will proceed."]
[/caption]
Subscribe to:
Posts (Atom)