All versions of Microsoft SQL Server (not MySQL). Please also add a version-specific tag like sql-server-2016 if that is relevant to the question.

learn more… | top users | synonyms (2)

3
votes
1answer
198 views

how to get the information about sleeping processes that are causing blocking?

I have a procedure that allows me to see what is currently running in my sql server. This procedure does not use sys.sysprocess because it has been deprecated. However, I am struggling to find ...
3
votes
1answer
232 views

Why is my filtered index being ignored?

--Setup, actual queries after the space --create table fltrind (a integer,b integer) truncate table fltrind DROP INDEX fltrind.nf DROP INDEX fltrind.f DECLARE @i integer SET @i=0 while @i<100 BEGIN ...
3
votes
2answers
10k views

Query (duration) different between data value change

I am trying to write a query that would get me the duration of a status in a table. This query needs to work in SQL Server 2008. Say I have the following table: Key Value RecordDate 1 1 ...
3
votes
2answers
8k views

Transaction Log Maintanance While Using AlwaysOn Availability Group

We are using HADR ( AlwaysOn Availability Group) feature of SQL Server 2012. Server and AG Configuration as below: SQL12_SRV1 --> AG1(PRIMARY) - SYNC -->> DBTest SQL12_SRV2 --> AG1(SECONDARY) ...
3
votes
2answers
1k views

Server permissions of an activation stored procedure of a Server Broker queue

I have a stored procedure that queries the sys.dm_exec_requests view. In the stored procedure the view only returns one row, while the stored procedure needs to see all of them. The MSDN article on ...
3
votes
3answers
24k views

Moving SQL Server to different domain

What are best practices/important steps when the Windows Server machine hosting SQL Server 2008 R2 is moved to another domain? What steps definitely/might need to be done for SQL Server before and ...
3
votes
3answers
3k views

View foreign key constraints so I can delete a table

I have copied a database I was working on so I could change some key elements of its design for my new version. I want to delete totally some tables from the database however I cannot due to some ...
3
votes
2answers
2k views

Move (Restore) SQL Server 2005 database which does not exist before

I did not ready about this but found out after trying to restore a database from remote server and it wont restore if the database did not exist before. What I would do is create a .bak file ...
2
votes
1answer
71 views

Troubleshooting memory pressure - how to avoid paging out SQL Server?

I have received the email below that states that an alert has been fired and a significant part of SQL Server process memory has been paged out should I raise the Minimum server memory to 1740 ...
2
votes
1answer
58 views

Trace flag is running, but not message when deadlock happens

Trace flags are used to temporarily set specific server characteristics or to switch off a particular behavior More information on the trace flags are here DBCC TRACESTATUS this above, is ...
2
votes
3answers
411 views

Undo SQL UPDATE Azure DB

I have accidentally updated over 3,000 records in one of my tables hosted on Azure. This was not a transaction and I did not have any back ups for this table. I've read around and found ApexSQL Log ...
1
vote
3answers
98 views

Alter DB modify log file for all DBs

I can run this command to make changes for 1 DB USE [master] GO ALTER DATABASE [DBName] MODIFY FILE ( NAME = N'Name_log', FILEGROWTH = 10000KB ) GO Is it possible to create a script which changes ...
1
vote
1answer
215 views

Correct way to uninstall SQL Server 2008 ( or above )

Since University I've been facing this problem... How do I uninstall SQL Server? What I've done is delete everything in "control panel" related to SQL Server. But, is there a right way to uninstall ...
1
vote
2answers
335 views

SQL 2008 Server - performance loss possibly connected with a very large table

We have SQL Server 2008 with 3 active databases running on it. DB1 - cca 400 MB size DB2 - cca 8 GB size DB3 - cca 42 GB size - but the majority of records are not used at all In DB2 we have a ...
1
vote
2answers
1k views

SQL Server Log Shipping: File copy options?

I usually use redgate sql backup which has a transaction log copy mechanism built in. If I roll my own log shipping, what are established ways to move the log file? I want to emphasize: The way log ...
1
vote
3answers
775 views

Improve performance by partitioning

I'm using SQL Server 2012. I’m using CTE to create an orders table from the items table. The final count for the orders table is around 120 million. It is taking about 2 hours for the whole process. ...
1
vote
1answer
7k views

Set permissions on all objects for SQL users

I have a procedure that loops through all objects in the database and assigns them proper permissions to that object. I want to know if there is a better way to do this? I use a model database to ...
0
votes
3answers
221 views

Is it possible to restore database only with .trn files?

How do I restore database only with .trn (transaction log backup) file? I did below two steps Microsoft SQL Server Management studio shows "Restoring". 1) RESTORE DATABASE [database_name] FROM ...
0
votes
1answer
499 views

can i shrink log file in full recovery model

i have configured Always on Availability group where iam unable to shrink log file size through simple recovery model. can i shrink the log file size in full recovery model if yes or possible then is ...
0
votes
2answers
4k views

Check Constraint with Case Statement

I want to add a check constraint on a column if entered value for column is greater than 3 than it should be saved as 1 else entered value. For that scenario I am using below query but on insertion of ...
-2
votes
1answer
63 views

Need to list the Similar Sub Groups

Kindly help me to get the list of similar groups together. Please see the above illustration. Here, BaseItem 6586, 6587, 6588, 6589 are sharing similar kind of SubItems (SubSet 1, SubSet2, SubSet3 ...
21
votes
1answer
16k views

SLEEP_TASK Wait Type in SQL Server - What does it indicate?

I haven't seen the SLEEP_TASK wait type before, and today I seem to be getting a ton of them. I'm not the official DBA, just a SQL Server developer who knows some DBA stuff. We upgraded our servers ...
12
votes
2answers
3k views

INT or CHAR for a Type Field

What is the best design for a table, a Type field that is of int or char(1)? In other words, given this schema: create table Car ( Name varchar(100) not null, Description varchar(100) not ...
10
votes
1answer
869 views

Determine the SQL Server version and edition from the available set up, before actually installing it?

We are going through a downgrade process from SQL Server 2008 R2 Enterprise to Standard edition. To kick off the above activity, we were looking for available Standard Edition of SQL Server. We found ...
10
votes
4answers
12k views

Does SQL Server ever delete old error log files?

I know I can cycle the current error log easily by running sp_cycle_errorlog, but I'm wondering if SQL Server will ever delete the old/archived error log files at all. Can't seem to find an answer to ...
10
votes
3answers
5k views

Restrict access for Windows administrator

As the SQL Server best practices says, "Windows Authentication mode is more secure than SQL Authentication". And now I want to know: is there a way to protect SQL Server from user with Windows ...
9
votes
1answer
13k views

Handling exceptions in stored procedures called using insert-exec blocks

I have a stored procedure that is called in an insert-exec block: insert into @t exec('test') How can I handle exceptions generated in the stored procedure and still continue processing? The ...
9
votes
3answers
5k views

Security and Performance implications of “View Server State”

This question points out that "View Server State" permission is required for various DMV's (dynamic management views), but I can't find anything about who you do and do not want to grant the ...
9
votes
1answer
2k views

Do schema changes “break” SQL Server 2012 AlwaysOn or are they handled transparently?

My organization is planning to adopt SQL Server 2012 AlwaysOn and I am trying to understand what impact (if any) it will have on our application upgrade process. We release application updates on an ...
9
votes
7answers
971 views

Scheduled backup task doesn't always backup all databases despite always saying job successful

I have a job in SQL 2008 that runs a stored proc to backup all databases. This runs daily via sql server agent job. It quits with success every day but some days it quits with success only after ...
9
votes
2answers
2k views

Unique Constraints on Nullable Columns in SQL Server 2005

In this one project I am working on, I need to set a particular field to be unique (not a problem!) but if the field is null I want the constraint to be ignored. In Sql Server 2008 I use filtered ...
8
votes
6answers
8k views

Get last full backup and transaction log backup for each database

How to write script or a query that will display the last full backup and the last log backup for each database on each of the production servers using the system table "backupset" and related backup ...
8
votes
4answers
5k views

Linked server error not caught by TRY-CATCH

I am setting up a job to loop through a list of linked servers and execute a specific query against each one. I am trying to execute the query inside a TRY-CATCH block so if there's a problem with one ...
8
votes
3answers
47k views

SQL Server Restore a SINGLE table from a backup [duplicate]

on SQL Server 2005, we do a weekly full backup with nightly incremental backups. I want to know if it is possible to restore a single table from a backup, either to the source database or a ...
8
votes
3answers
9k views

How to free SQL Server memory?

I have a SQL Server sandbox installation on my local computer. I have set the memory SQL Server instance can use to 1000 mb. When running an intensive operation, memory usage rises up to 1000 mb. When ...
7
votes
1answer
1k views

Why does the order of parameters matter for sp_trace_create?

The first batch of the following script calls stored procedure sp_trace_create with parameters in documentation order; the second batch swaps the positions of parameters @tracefile and @options: ...
7
votes
2answers
1k views

Advantage of using a database snapshot for reporting purposes

What is the performance advantage of using a snapshot of a database for reporting purposes? The way I see it, it would probably downgrade performance since for every write in the original database, ...
6
votes
1answer
22k views

QUERY - pivot multiple columns, variable number of rows

I have a table that looks like this: RECIPE VERSION_ID INGREDIENT PERCENTAGE 4000 100 Ing_1 23,0 4000 100 Ing_100 0,1 4000 200 ...
6
votes
1answer
157 views

Does RCSI use less or same space than SNAPSHOT? Why?

Trying to better understand row-versioning based transaction isolations - RCSI (Read Committed Snapshot Isolation and SNAPSHOT) - in SQL Server... MSDN online article "Choosing Row Versioning-based ...
6
votes
2answers
8k views

Permission required to view a database diagram

I have recently set up SSDT for our developers to use. We enforce changes to our dev databases are made through SSDT by limiting the permissions that each developers has when connected to the server ...
6
votes
1answer
684 views

How does SQL Server order results when joins are used?

How does SQL Server figure out the order of the records in the result set of query execution? I am trying to make heads or tails of it but find myself scratching my head. When I change the fields I ...
6
votes
3answers
3k views

SQL Server virtual log size

I'm somewhat of an accidental DBA, being a developer who inherited a couple of database servers (2005 and 2008) from someone who knew little about database administration, and seemingly had even less ...
6
votes
2answers
2k views

How can I save a severely fragmented database/table?

I've recently made a discovery that might explain my slow SQL run times. I found out that the default in SSMS is to allow mdf growth of 1MB at a time. When the size of one table alone is roughly 23 ...
6
votes
2answers
481 views

Does the order of columns in a columnstore index matter?

I have a table with ~200 million rows and ~15 columns in it. I am planning to create a COLUMNSTORE index on my table. Will there be any change in performance based on the order of columns that I use ...
6
votes
5answers
8k views

Can I be automatically notified of prolonged blocking in SQL server?

About once a week I have to resolve a blocking chain on a SQL Server 2005 database, caused by a long-lived read lock from an Access 2003 front-end. The lock is taken out whenever a user opens a ...
6
votes
1answer
3k views

What's difference between standard view and indexed view in SQL Server [closed]

I learn that the indexed view holds the data from base tables while standard view does not holds data.Is it means that the indexed view need memory and standard view don't? Indexed view needs to be ...
6
votes
1answer
591 views

How to back up a small database in SQL Server 2008 R2 Express Edition

I will shortly be migrating a database from SQL Server 2000 (part of SBS 2003) to SQL Server 2008 R2 Express Edition The database is small, and there are only a few hundred short transactions per day ...
5
votes
2answers
411 views

Reseed identity column in all empty tables

I need to write a query that resets the identity on all tables that have an identity column and are empty.
5
votes
1answer
1k views

How do I track logins, connections, user accounts, queries, …?

I am a BI person who has been asked to step in and pretend I am a DBA. I am not a novice with databases but AM a novice at the admin type stuff. We have a SQL Server database that is going to be ...
5
votes
2answers
28k views

How can I dynamically alias columns?

I have a table (not designed by me) which has 20 variably named columns. That is, depending on what type of record you are looking at, the applicable name of the column can change. The possible ...