- Select each category on the left to see all the articles for each category.
- For all the articles on Microsoft SQL Server use the Site Map
- And don't forget the feedback form.
Why you should NOT shrink an MSSQL database
- You can shrink MSSQL databases with the command: dbcc shrinkdatabase (xxx).
- See: DBCC SHRINKDATABASE with MSSQL
- dbcc shrinkdatabase (xxx) will reduce the size of the database by moving the data in the file from the end of a file to the unallocated pages at the front of the file. This is fine as long as there is no index involved.
- If the AUTO_SHRINK turned on, the dbcc shrinkdatabase process will start up every 30 minutes and try to shrink each database that allows for the AUTO_SHRINK.
- If the database is a read/write operation it will grow in size when adding new data, and will try to shrink again 1/2 hour later.
Connection vs Session_ID
Each session is associated with to connection. A session_id, or SPID, is assigned to each connection. The SPID remains constant to that connection for its lifetime.
You can see the connections it the dynamic management view: sys.dm_exec_connections.
Applies to:
- Microsoft SQL Server 2005
- Microsoft SQL Server 2008
In which order does SQL Server process SQL statements
The main SQL/Select statement is:
[@ SELECT DISTINCT TOP (list) FROM (LeftTable) (join type) JOIN (RightTable)
How to completely empty the transaction log with MSSQL
There are occasions when you need to completely need to empty the transaction log, such as shipping the data to another location where the data will later be synchronized. Try sending a 1Tb database with a 75Gb log file.
- You should do a backup of the database before emptying the log file.
- You will be deleting all the uncommitted transactions, so watch out!
... click here to continue ...
What are the transaction in the log
Often I need or want (it's more a want than a need) to know what are the transactions that are stored in the log. Microsoft provides the dynamic management view: sys.dm_tran_database_transactions that keeps track of the transactions that are stored in the log file. IMHO, the most important columns are:
- database_transaction_log_bytes_used
- database_transaction_log_bytes_used_system
- database_transaction_log_bytes_reserved
- database_transaction_log_bytes_reserved_system
- database_transaction_log_record_count
These figures will tell you how healthy is your throughput and if you should be concerned.
Row count for all the tables in a database with MSSQL
People often request reports or views that have everything. In the old days it was a few hundred or a few thousand of rows, today it's millions and billions of rows. Many of these reports go straight to the printer. I don't have to imagine, a customer mine recently did it: 3 million rows to be printed, without looking and checking that what he needed. He aborted the report after printing 5,000 pages.
Applies to:
- Microsoft SQL Server 2000
- Microsoft SQL Server 2005
- Microsoft SQL Server 2008
Inaccurate row count with MSSQL
Microsoft SQL Server does a lot of processes in the background. Sometimes, actually quite often on very heavy workload, the background processes run behind schedule to a point where the background operation is not done. Updating index statistics and row counts are some of these background tasks. Microsoft provides the dbcc updateusage to updates its own stats.
dbcc updateusage reports and corrects pages and row count inaccuracies in the various catalog views. This often leads to incorrect data from sp_spaceused system stored procedure.
Applies to:
- Microsoft SQL Server 2000
ShrinkDB vs. Backup for MSSQL
Shrinking your log files is usually a bad idea. Normally the log file will assigned a fixed size during the database creation. The log files contain both all the uncommitted transactions and the committed transactions. During the backup, the committed transactions are removed from the log file. Still there are times when you need to shrink the log files. Microsoft offer 2 ways of shrinking the log files: dbcc shrinkdb and backup log
Applies to:
- Microsoft SQL Server 2000
- Microsoft SQL Server 2005
Differences in performance monitor between MSSQL 2000 and 2005/2008
Applies to:
- Microsoft SQL Server 2000
- Microsoft SQL Server 2005
- Microsoft SQL Server 2008
Finding duplicate rows with MS SQL Server
MS SQL Server does not have a built-in function for finding duplicate rows, so the standard way of doing this, is through counting.
Applies to:
- Microsoft SQL Server 7
- Microsoft SQL Server 2000
- Microsoft SQL Server 2005
Inline views with MS SQL Server
Summary
- Aliases are most often use to simplify somebody's life, either the programmer's or the end-user's.
- What is clearer : VMT003 or Vertical Maintenance
SmallDateTime vs DateTime
Size
- A SmallDateTime data type is a 4 bytes date and time.
- A DateTime data type is a 8 bytes date and time.
Date range
- SmallDateTime: From January 1, 1900 00:01 (AM), until June 6, 2079 23:59 (PM)
How to get the date portions from datetime with SQL Server
- Microsoft SQL Server, like almost all good SQL servers, has very rich date functions.
- The date functions are 'NOT' SQL2003 compliant.
- The date functions follow MS Visual Basic. This means that all of the date functionality will have to be rewritten when Microsoft moves to SQL2003 compatibility.
Date calculations
... click here to continue ...
SQL: very slow negative queries
Problem
The SQL negative queries take forever.
- SELECT empno, salesqty, salesprice FROM sales WHERE not (sal.salestype = 'B');
- go
How to cover a business week
- Management often is not interested in the date, but is much more interested in the week. The week is was allow them to plan for: such as labour, cash flows, inventory variations...
- The trick is convert the specific dates to Mondays and to Fridays.
- Microsoft SQL Server provides the datepart weekday to return numeric value of the day of the week.
- The numeric value of the day of the week also depends on the setting: which day is the 1st day of the week? Is it Sunday [the default for US English]? Or Monday? You will find out by using the @@datefirst function.