Microsoft's SQL Server is a relational database management system (RDBMS) that runs as a server providing multi-user access to a number of databases. It originated from the Sybase SQL Server codebase, which is why both products use the extension of SQL called Transact-SQL (T-SQL).

learn more… | top users | synonyms (2)

1
vote
1answer
864 views

Syspolicy_purge_history connection issue

I am having an issue with the syspolicy_purge_history SQL Agent job on a 2008 SQL Server system. The server is using a non default port number and is in a cluster. Before it was put in a cluster, it ...
6
votes
2answers
2k views

SQL Server Partitioning vs Indexes on Separate Filegroups

I am just about to start on an excercise to reorganise a partitioned table and I was looking for best practises or recommendations. Let us say I have a billion row table and 8 logical disks available ...
6
votes
1answer
10k views

Database 'MyDb' is being recovered. Waiting until recovery is finished

I got so error: Database 'MyDb' is being recovered. Waiting until recovery is finished. I have had a critical operation at that time. What is the cause of this situation? And how can it be ...
1
vote
1answer
2k views

How to move data from Primary Data File to other data file without destroying replication

I have one Database which is replicated using transactional replication. Now, I want do spare some data of that database to another partition, because my server running out of disk space. I was ...
2
votes
5answers
4k views

Keep transaction log size under control during batch processing

tl;dr I think my verbosity has obscured the real question I'm asking here, so I apologise for that. My main issue is that the checkpoint command appeared to be working for at least 800 iterations of ...
2
votes
3answers
704 views

Column sort order in CREATE INDEX statement?

When creating an index one can determine if an index column is sorted using ascending or descending order. BOL says that [ ASC | DESC ] Determines the ascending or descending sort direction for the ...
3
votes
1answer
159 views

SQL Server - Using spatial data

I'm using SQL Server 2008 R2 for real-time location aware service. The service mostly receives location data (lat/lon) from a user and returns locations around the user. The data is being sent from ...
2
votes
5answers
6k views

Small sample database for practicing database administration?

I'm looking for a portable sample database that can be created from a script has a simple ER model has little data for database administration practicing purposes. I know that there is e.g. ...
2
votes
2answers
2k views

6th normal form, Recomposition query, efficient implementation

I found this paper written by Hugh Darwen for avoiding nulls in my database: Link, It describes how to implement databases in the 6th normal form so you can avoid nulls. The logic is described in the ...
0
votes
1answer
197 views

Multiple Partition Keys?

This is a follow-on from a previous question. I've never worked with SQL Server partitioning but I currently faced with designing a database for which the volumes probably warrant it. The system is ...
2
votes
2answers
4k views

Should I lock the table during delete

I have a table that has 4 million records. It has a clustered index on a date column (record creation date). It has 5 tables reference this table, all have FK indexes. The machine has no down time. I ...
1
vote
1answer
369 views

What is the effect on primary keys when moving a CLUSTERED index to a new filegroup?

I'm moving a CLUSTERED index from one filegroup to another, using the with drop_existing=on functionality. Do I need to be concerned about primary keys? In other words, is there some special ...
2
votes
1answer
610 views

How to find what tables are stored in what data files

I know that tables can span across multiple data files The tables are already created. I did not create them myself. I'm trying to find out what table(s) are using the highest amount of IO. I was ...
1
vote
1answer
1k views

SQL Server 2005/2008 logging connections

How can I audit SQL connections to the database from Windows credential Sql Users Is there some built-in feature where I can determine number of: all connection active connection unclosed ...
5
votes
2answers
1k views

Benefits of making a sql server database as read-only?

I am using a reporting database which for all practical purposes is a read only database. It is created data and dynamic reports are generated and viewed on this database. I was thinking of making ...
6
votes
3answers
303 views

Query performance

Why SELECT Barraportfolioname FROM portfolio WHERE id IN (SELECT DISTINCT i1.portfolioid FROM Import i1 LEFT OUTER JOIN Import i2 ON ...
3
votes
1answer
804 views

SQL Server Alert Based On Severity Level Not Working

I am new to SQL Server alerts, and I am trying to setup an alert That will run a job and send a notification, based on severity level. In order to test if the alert works correctly I have downloaded ...
3
votes
1answer
2k views

What does Error: 17828, Severity: 20, State: 4 mean?

I've found this guy in my SQL log: "The prelogin packet used to open the connection is structurally invalid; the connection has been closed. Please contact the vendor of the client library. [CLIENT: ...
16
votes
4answers
11k views

Should dbo schema be avoided?

When it comes to the dbo schema: Is it a best practice to avoid using the dbo schema when creating database objects? Why should the dbo schema be avoided or should it? Which database user should own ...
2
votes
2answers
113 views

SQL index with additional includes

In an effort to be more efficient when creating a new index, if there is already an index with the same index keys, but it simply needs additional include columns, is it better to create a second ...
10
votes
6answers
5k views

Suddenly Slow Execution Plan for Stored Proc

I'm trying to understand an issue we're having with SQL Server 2000. We are a moderately transactional website and we have a stored proc called sp_GetCurrentTransactions which accepts a customerID, ...
5
votes
2answers
280 views

If a query triggers a statistics update and times out are the statistics still updated?

I have a fulltext query which is usually very fast but may time out when it causes a statistics update since statistics updating is very slow on this database. Usually the query "recovers" to normal ...
0
votes
1answer
139 views

Set timeout expiration in ms sql 7

I'm trying to perform a query in a transactional database that was used for more than 3 years. But it always says timeout expired after running for about 30 seconds when I try to run this query: ...
11
votes
1answer
2k views

Why is Clustered Index Scan Number of Executions so high?

I have two similar queries that generate the same query plan, except that one query plan executes a Clustered Index Scan 1316 times, while the other executes it 1 time. The only difference between ...
1
vote
1answer
751 views

Can't run query using “localhost\sqlexpress”

I am trying dynamically build an SQL Statement that includes linked server. That works. When I use "localhost" to reference the current local machine SQL Server, that doesn't work. For example: ...
4
votes
2answers
1k views

SSAS queries time out when Windows File System Cache drops

We have a SQL 2008 Analysis Services instance on Windows 2003 that is experiencing near-daily performance issues (query timeouts) during peak activity. A second identical instance queried from the ...
3
votes
3answers
2k views

Unable to get DBMail working on SQL Server 2005

I have SQL Server 2005 SP3 (4053) running on a Windows Server 2003 R2 virtual machine. When trying to send a test dbmail message, either from the SSMS GUI or using sp_send_dbmail, I get the ...
3
votes
1answer
264 views

Does blocking always mean open transaction?

This may seem a bit dull question but does blocking always mean that there is open transaction and that may cause transaction log grow to infinity because open transaction prevents log truncation ...
5
votes
1answer
328 views

What 'resets' the transaction log

My question might sound rather strange but I'm not really a SQL expert and I can't find the answer.. I'm trying to create a backup plan which creates a full backup twice a day. A differential back-up ...
0
votes
1answer
721 views

Getting the metadata about a Query and a Stored procedure in SQL Server

I would like to get all the names of the columns in a given query [SQL Textual Query] and also the columns that are in the SELECT clause in a Stored Procedure. Is there any ways other than using ...
1
vote
1answer
331 views

How to take Snapshot of Restoring Database

I have set up log shipping with my database to make a backup. The backup copy of the database is in a status called '(Restoring...)'. I understand this is to be expected? Is there a good way I can ...
2
votes
2answers
390 views

How do you restore (or check) only the extended properties in a MS SQL Server .Bak file?

We store the version number of our DBs as an extended property. As part of the automated build process, I want to be able to check that the "empty" DB that is part of the build set matches the version ...
2
votes
2answers
2k views

How To Restore A Replicated Database

I am replicating our live database using transaction replication. This is basically to have a backup copy of our database in the case of server failure. The replicated database is read only, so I ...
2
votes
1answer
88 views

Data Extract and submit across the network

I've been given a somewhat vague requirement regarding a data extract process which I need some help with. I'm fairly new to database warehousing etc. but I will try to explain everything relevant. ...
4
votes
2answers
1k views

How do you use MAXDOP in a View?

I have several views that are used to export data from SQL-Server into .csv files. They are all executed via SSIS packages. Our DBA has decided to set the "Max Degree of Parallelism" to 1 and told me ...
3
votes
1answer
544 views

SQL Globalization - DateTimeOffset

What is the recommended best practice when it comes to storing date/time for an application that needs to support multiple timezones? Which is best ? Use the SQL DateTime data type and insert ...
1
vote
1answer
981 views

How often should I run integrity and consistency checks on SQL Server 2008?

Before updates of our application, I run some checks on the database like CheckAllocations and CheckCatalog, so I know the database is in good shape before the update scripts get executed. What is ...
2
votes
2answers
3k views

Sort by match of LIKE

I wondering how I can implement SQL to get results sorted by best match of a like predicate. I have 100K articles in the database and when user call some items by part of name. I want to show the ...
0
votes
1answer
1k views

SQL Server 2012 Installation Error

I been trying to install SQL Server 2012 on Windows 7 SP1 32 bit. Logged in as Administrator and launched setup. Everything installs fine but SQL Server DB Services fails with following error ...
11
votes
6answers
3k views

Reasons for disabling statistics auto update?

I just learned that a client company I work for has decided to keep the auto update statistics options off for some of their SQL Servers, and the DBAs manually troubleshooting performance issues when ...
1
vote
2answers
6k views

Connect to SQL Server Management Studio over VPN (Hamachi)

I just got my Hamachi VPN set up. For anyone familiar with Hamachi, I have it set up as a gateway so I'm part of the network when I'm away. Almost everything seems to be working perfectly. I can ...
6
votes
2answers
3k views

Best block/stripe size for log volume?

I know the common recommendation for the data volume in SQL Server is to use 64 KB blocks/stripes, since the I/O is typically done by entire extents. I can't find any good information regarding log ...
1
vote
2answers
145 views

Restore backup in order to write datafile sequentially in disk

I have just come to a new company and we have one database which was created with standard initial size and autogrowth parameters for data and log files (1 MB autogrowth for data / 10% for log). The ...
1
vote
1answer
559 views

SQL Server Database Keeps Semi-Detaching Itself

Forgive me if I am not using all the proper terms but I have databases that will detach themselves from SQL Server. In SQL Server Management Studio the plus sign will be missing next to the database, ...
4
votes
3answers
552 views

Most performant SQL query needed

I'm storing documents with fields that are set in a DataSource table. The document is written to the database as follows: Document table (ID, DataSourceID) Document fields (DocumentID, ...
3
votes
1answer
300 views

How Commit works in a transactional push replication with updatable subscribers

I want to know if in a transactional push replication with updatable subscribers, a commit is only done when all subscribers have succeeded to make it, or if it only guarantees the correct commit in ...
2
votes
1answer
204 views

Dropping then adding a table to a subscription with Identity option Not for Replication causes errors

We have a Transactional Replication environment that was initialised from a backup that replicates the data for several hundred tables. When a table is altered, say a new column added, I have created ...
2
votes
1answer
1k views

What are the names of SQL Server database statuses 24, 1024 and 65544?

using the following query: SELECT Name, Status FROM dbo.sysdatabases WHERE name IN ( 'db_name' ) I get in some cases status = 24, other times 1024 and other times 65544 I see that even when status ...
6
votes
1answer
193 views

DBCC TRACEON with the global flag

In the question Need Help Troubleshooting Sql Server 2005 Deadlock Scenario there was a suggestion of using DBCC TRACEON (1204, -1) to trace deadlocks globally. When reading about this command in BOL ...
1
vote
2answers
703 views

SQL Server AlwaysOn Failover Transparency

This is using RC0. I have set up a SQL Server 2012 AlwaysOn (or more accurately, highly-likely-but-not-always-on) Availability Group. I take the Primary offline. The Secondary takes up to 25 ...