Tagged Questions

3
votes
6answers
107 views

Backup very large table

I have to update certain values of a large table (for the sake of a presumed example, it is called 'Resource' and it is over 5M rows) and thus I have to make a backup before performing the changes. We ...
-1
votes
1answer
33 views

Directory lookup failed during log shipping

I have configured log shipping for all our SharePoint 2010 databases. All worked well since Friday and today Monday I ran this query: SELECT * FROM [msdb].[dbo].[sysjobhistory] WHERE [message] like ...
1
vote
1answer
126 views

Upgrade SQL Server 2005 Enterprise to SQL Server 2008 R2 Standard

I understand that this is not a supported path, but does anyone have any insight about how to accomplish this? I can't seem to find any documents on the subject. I need my QA (2005) to match my ...
1
vote
1answer
38 views

Moving one TempdB on 3 instance server

I have a large virtualized SQL Server (Full 2008R2). I run 3 SQL instances and would like to relocate the TempdB database file to another location, splitting off from the TempdB log file. The trouble ...
1
vote
2answers
138 views

Reducing high VLF count

I have been using Brent Ozar's sp_blitz script to review our database setup and have found that the VLF count is high (13562!) on the primary DB since changing to Full recovery model. I ran a full ...
2
votes
4answers
71 views

What is the best permissions to set for only creating and managing own databases?

I want to create a login for a new user who could only create and manage their own databases. Other databases on the server should be read-only to that user. What would be a good set of ...
1
vote
1answer
60 views

dead lock when updating

Update Operation.TrTable Set RecordId = RecordTABLE.newRecordId From Operation.TrTable tr Inner Join ( SELECT r.Id AS newRecordId, r.KeyM AS MappingKey From ...
1
vote
1answer
92 views

Moving model database

I have moved the model database by using following method For each file to be moved, run the following statement. ALTER DATABASE model MODIFY FILE ( NAME = modeldev , FILENAME = ...
1
vote
2answers
33 views

SQL Server database backup on Remote site

We have 4 different sites and each site has a SQL Server. our requirement is to have a SQL Server backup or database(Mdf files) of site 1,2,3 in site 4. In case of disaster on any of the site we ...
2
votes
1answer
100 views

sql server partitioned view execution plan questions

I have created a partitioned view for 3 tables tb_sales2010,tb_sales2011,tb_sales2012 using check constraint (respectivly for each table) datepart(year,[Date])=(2010) datepart(year,[Date])=(2011) ...
5
votes
2answers
255 views

Recreate Indexes on 1 billion record table

I have a table with over 1 billion records and it has 6 indexes (including Clustered index (ID)). I need to partition this table on a new Clustered index with date column. I have just enough space ...
3
votes
1answer
131 views

Automatic database file creation based on database file size in SQL Server 2008 R2

Now i am using SQL Server 2008 R2 database for data logging. The name of the database profile is MASTERDB. That profile has one data file called MASTERDB.mdf and one log file called MASTERDB_log.ldf. ...
1
vote
1answer
56 views

Would I lose data if I change a column from varbinary(8000) to varbinary(max)?

Is there any data loss if a table is being altered to change an existing column from varbinary(8000) to varbinary(MAX)?
1
vote
1answer
326 views

SQL Server Login failed for user Error: 18456, Severity: 14, State: 11

I have an AD group XYZ that I have added to SQL Server security with data_reader permissions. The XYZ group has around 10 users in there who are successfully able to access the SQL Server database. I ...
1
vote
1answer
81 views

Error creating scalar function “Invalid Object Name dbo.fnMyFunctionName'”

I am using SQL Server 2008 R2. I tried creating a new scalar function. The function gets created and I see it in the list of scalar functions. When I try to modify function, function name is ...
1
vote
0answers
91 views

Collect CPU, reads and writes when replaying SQL Profiler Trace

I'm trying to replay a trace on a test environment with SQL Profiler in order to benchmark a database prior to making some performance tweaks. I intend to replay the same trace at various stages to ...
2
votes
1answer
189 views

SQL Server cluster questions please

We have SQL Server 2008 R2 cluster active/active/active (a, b, c) and three instances run on this environment. We did a test on one of the instances, and failover test. Manual failover from node c ...
0
votes
1answer
57 views

Any issue in referencing a unique index from a foreign key table?

Is there any concern in referencing a unique index from a foreign key table? Using SQL Server 2008 R2 Please advise thanks
4
votes
1answer
71 views

comma delimited column fix

At my current place of employment someone made a decision to put IDs into a comma delimited column. I'm looking to break these out into a proper many to many relationship. However; it is not in the ...
5
votes
2answers
496 views

How export a sql server 2008 diagram to PDF filetype?

I want to have a export from my database diagram to PDF or image types.How can I do this? I worked with sql server 2008 R2.
2
votes
0answers
488 views

Error 9001, Severity: 21, State: 1 The log for database 'DB_NAME' is not available

I have a SQL Server 2008 R2. I got a mail that the backup had failed, I opened the SQL log viewer and started browsing. And i found these two errors a bunch of times. error 1: Date ...
2
votes
2answers
77 views

SQL Server: reset all database tables

I have a SQL Server 2008 R2 database with about 60 tables and around 300 columns. I want to reset all the data and auto increments, I found a question somehow similar but the answer there is to ...
-3
votes
1answer
101 views

Sync in structure of two database [closed]

I am using SQL Server 2008 R2. We have two database, one is development and another one is for testing. We want to maintain both databases in sync but only in structure and stored procedures, not in ...
1
vote
1answer
122 views

SQL Server Error 833 On LDF File

I'm getting error 833 on my ldf file and i'm getting it a lot. Please see the following. When this happens, my application is hang already. I know error 833 has been reported a lot, but what does it ...
1
vote
1answer
52 views

Unique Non Clustered Column in Partitioned table

We have partitioned one of our SQL Server tables on a Clustered Index datetime column, we have a Primary key Non-clustered index column with Identity data type and this column is setup as a ...
1
vote
3answers
108 views

SQL Server BPA 2008R2

I have been using SQL server BPA for getting good information from Microsoft. I was using its 2005 version in which i used to export the results in csv format but recently I got two new servers which ...
2
votes
1answer
171 views

Error while restoring database in SQL Server 2008 R2

This error occurs while I restore database from .bak file A system assertion check has failed. Check the SQL Server error log for details. Typically, an assertion failure is caused by a software ...
0
votes
1answer
71 views

How to create an index view?

I have a view which is doing summation of several columns: Select cola, colb, cola + colb + colc from table tbl_a Can I know what are the indexes I should create for the view to improve its ...
1
vote
1answer
167 views

Right Trimming Binary Data in SQL Server

Scenario: I am inserting a string into a binary field (CONTEXT_INFO) and then later attempting to pull it out and convert it back to a string. When I do, the resulting string has a length of 128 ...
1
vote
2answers
275 views

Multiple triggers vs a single trigger [closed]

Scenario: Each time data is inserted/updated/deleted into/in/from a table, multiple unrelated items of business logic need to be executed. Question Given that the solution is to use database ...
1
vote
1answer
79 views

Restore .mdf data file for a File Group in SQL Server

I have a database with three filegroups and each filegroup hosting one datafile. One of the secondary filegroups XYZ with data file xyz.ndf got updated with wrong/bad information in various tables ...
3
votes
3answers
154 views

SQL Server 2008 R2 Restore a back up to a new database

I have a full back up of a database, I want to update some records in some table in the current database from its old backup without affecting other records. What is the best way to do this? ...
5
votes
1answer
99 views

Practical application of EXISTS vs. LEFT JOIN to find addresses for delivery

Our end users have many ways of shipping product to our customers. They can use the customer address in one table ([customer]), a specific delivery address in another ([dropship]), or yet another ...
5
votes
1answer
352 views

SQL Server 2008 R2 DMV Question

I have a query to retrieve the Top 10 queries based on total logical reads from the dm_exec_query_stats DMV. SELECT TOP 10 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1, ((CASE ...
1
vote
1answer
429 views

SQL Server - Varbinary Column - Extremely Slow Statistics Update

I have a table in my database that we use as a filestore, the file itself is stored in a varbinary column, which all seemed to work well until recently, we noticed one of our instances of this table ...
2
votes
1answer
359 views

What are SQL Server Securables?

I migrated two applications to the new server. One of the them works without any issues but the other ran into back end sql permission problems like The EXECUTE permission was denied on the ...
2
votes
1answer
253 views

SQL Server Maintenance Plan multiple database backups in a single folder

I am using SQL Server 2008 R2. I am trying to create a maintenance plan which backs up multiple databases into one single folder for a day. For ex. I need to backup db1, db2, db3 and dump all these 3 ...
2
votes
1answer
600 views

How to Convert Horizontal to Vertical Array?

I need to create a query (suitable for Standard Edition) that has data from multiple columns (Columns 1-6 with corresponding Date Started and Date Completed data) displayed vertically, but also has ...
5
votes
2answers
116 views

Running queries against multiple servers

I want to use a Central Management Server, with a server group underneith, to run management queries against multiple server. I know how to run a query against the whole group through SSMS, but I ...
-3
votes
1answer
54 views

What is Better SQL Server Configuration? [closed]

First think : MDF and LDF separate on raid 1 Second think : All in one raid5.
5
votes
2answers
205 views

One Complex Stored Procedure Causes Estimated Cost Exception

In SQL Server, I'm getting the following error "The query has been canceled because the estimated cost of this query (5822) exceeds the configured threshold of 300. Contact the system administrator." ...
1
vote
2answers
622 views

Error on SELECT - Primary filegroup is full

A SQL Server 2008 R2 database view unions several transaction table. The query has been slow over the couple of months and the dev team is planning to recreate the query. I need to ETL the results ...
2
votes
2answers
565 views

SQL Server 2008 R2 DDL Trigger to create DML Triggers on all new user tables

As the title says I would like to create a DDL trigger which in turn creates DML triggers for most new tables. The trigger I have so far will create triggers on ALL tables, which is not what I'm ...
1
vote
1answer
146 views

High Network usage on SQL Server port [duplicate]

Possible Duplicate: Find out what queries are causing the biggest amount of network traffic We are getting over 90 Mbps from SQL Server to each web servers on port 1433. We are using ...
1
vote
1answer
135 views

Query to compare 2 table results and filter extra records found in a table

I have two queries that I want to compare and get the extra rows found in in the first query that are not in the second plus 1. I need the DocEntryCN to perform some work. E.g. Query 1 SELECT ...
3
votes
2answers
251 views

Large Fact table and partitioning key dilemma

I have fairly large fact table (2 billion records, approx 120 GB). This table is not partitioned and the queries are very slow to respond. I am planning to partition the table and indexes. The table ...
12
votes
2answers
784 views

Performance Tuning for Huge Table (SQL Server 2008 R2)

Background: I have a fact table in UAT Phase. Objective to load 5 yrs of data in Prod (expected size 400 Mn records). Currently it has only 2 years of data in Test. Table Features: No of ...
4
votes
2answers
381 views

Is SELECT ROW_NUMBER() guaranteed to return results sorted by the generated row numbers?

E.g. consider the SQL query: SELECT A.[Name], ROW_NUMBER() OVER(ORDER BY A.[Name] ASC) FROM [FooTable] AS A Here I observe the results being returned sorted by A.[Name]. If I change the ...
2
votes
3answers
201 views

Is there a way to tell SQLCMD not to output rows to text or a file?

I am testing the behavior of a SELECT statement that returns 170,000,000 rows using SQLCMD. My goal is to cut out the time spent receiving the result-set on the client end (the time the process shows ...
1
vote
3answers
395 views

Putting SQL Server data files in a different partition

I have a SQL Server and I put the data files (the .mdf) on a different partition. Windows runs from C: and the data files and logs are on D: Is it advantageous or disadvantageous to separate the ...

1 2 3