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).
3
votes
2answers
956 views
Can I have different publishers and one subscriber in replication?
I have the same databases in three cities which each database have three tables:
1- tbl_worker
2-tbl_workshop
3-tbl_users
and cities are:city1,city2,city3
city2 and city3 insert informations in ...
9
votes
3answers
286 views
Any pointers on reducing my privileges in production but not making my job excessively difficult
Running SQL Server 2005 and 2008 on Windows 2008 R2.
We're going to be reducing privileges in production for developers - and I'd like to do the same for myself as a DBA, limiting rights to ...
2
votes
1answer
474 views
SQL Server 2000 Enterprise Manager - “Navigation to the webpage was canceled” error when trying to design a table
While working in SQL Server 2000 Enterprise Manager on a remote server today, I wanted to look at a table's columns, so I found the table, right-clicked on it and selected "Design Table".
Instead ...
1
vote
2answers
271 views
Determining sizes for Separated Partitions of programs, data and logs in SQL Server
As per the recommendations of the CIS benchmarks for SQL Server, I want to create separate partitions for the data, the programs and the logs on my SQL Server dedicated server.
Question is, what is ...
6
votes
4answers
6k views
How to monitor deadlocks
When do you start troubleshooting a SQL Server 2005/2008 deadlocks and how? The alert is turned on SSMS via SQL Server performance condition alert, objects->SQLServer:Locks, Counter->Lock Waits/Sec, ...
4
votes
2answers
607 views
SQL Server Wildcardless LIKE Optimization
In a web application using SQL Server 2008 R2 as the backend, I have several fields, that if present, add WHERE clauses to the query.
If I was to have all queries generated as like clauses, such as:
...
6
votes
2answers
293 views
In Oracle, can we restore a database onto the same instance with a different name?
In SQL server I can restore a database onto the same instance with a different name. Eg: If there is a database DB_prod, I can take a full backup and restore it on the same instance with a different ...
4
votes
3answers
1k views
Query to normalize table/combine row text
I have a table (call it oldTable) with columns like so:
ID (int),Rank (int),TextLineNumber (int),SomeText (varchar)
The primarykey is multi-part: ID+Rank+TextLineNumber.
I'm trying to ...
6
votes
3answers
2k views
Dropping a constraint (index) on a column
How can I modify the type on a table that has an index on it? I tried to do an alter column on an empty table to modify the type from date time to varchar(15) and got errors saying that it had ...
6
votes
1answer
406 views
Dependant T-SQL comparison collation
Is there any way to set the collation for a string comparison based on an independent variable or column value? Something akin to
SELECT COL1, col2
FROM [TABLE]
WHERE COL3 LIKE '%string%'
...
14
votes
4answers
11k views
SQL server suspect database?
What do you do when you have a database that is marked as 'suspect'? Restore from the last backup? Please advise. Thank you!
3
votes
2answers
266 views
How can I extract a lessequal char from SQL_Latin1_General_CP1_CI_AS?
When I create a table like this
create table char_test(
item varchar(10) collate SQL_Latin1_General_CP1_CI_AS
)
go
I can store varchars containing ≥
insert into char_test values ('≥');
When ...
1
vote
2answers
175 views
Best index strategies for read-only table?
I have a table in SQL Server which has following characteristics:
will contain about 1.2M records.
first is empty, will be inserted in batch many times (precisely, 64 times), 20-100k records/each
...
3
votes
2answers
1k views
datatypes in sql server , difference between similar dataypes, numeric, money , decimal ,float
What datatypes could be given to this value --> 45.58
Could these be given:
numeric
money
decimal
float
What's the difference then? Which one would be the best to use?
3
votes
1answer
852 views
Why is this rollback needed when using sp_addextendedproperty in a stored procedure?
In enter link description here I showed, how I document databases.
To insert the extended properties, initially I uses plan sequences of sp_addextendedproperty calls.
But lately I wanted to catch ...
3
votes
4answers
3k views
Property PopulationStatus is not available for FullTextCatalog… How to fix?
I've created a new catalog (case insensitive, make default catalog), when I try to right click > properties to add tables I get this error:
Property PopulationStatus is not available for ...
3
votes
1answer
777 views
A nonrecoverable I/O error occurred on file after DB Shrink
After shrinking an SQL Server database & files, I encountered an error message saying "A nonrecoverable I/O error occurred on file" while performing database backup.
Why am I getting this error?
14
votes
3answers
496 views
How to store 'n' days of web server logs in Sql Server?
For faster reporting and performance analysis, we want to insert our web server logs into Sql Server. This will allow us to see traffic patterns, issues, slowdowns in near real-time.
We have a daemon ...
5
votes
1answer
2k views
SQL Server Deadlock Graph - Table, Page or Row Lock?
Is there any way to decipher if a lock in a deadlock graph is Table, Page or Row level? I have all the information I need from the graph, including the Isolation Level, (2) but I really want to know ...
6
votes
6answers
1k views
SQL Server 2005 Huge DB issue
My DB files are growing very fast ( designer is not me and just a weekly report makes 7 GB growth ). And finally, I don't have enough space in the disk. And number of un-reported weeks counting up.
...
3
votes
3answers
586 views
Most suitable DB Architecture for real-time high-volume processing API
I've got a service that should support real-time interaction between many users (thousands real-time). I use .NET 4 with MSSQL 2008 on IIS 7.5.
The DB consists of only a few tables without high usage ...
15
votes
7answers
12k views
How can I tell if a SQL Server database is still being used?
We're looking to decommission a SQL Server instance which has a couple databases still remaining on it.
How can I tell if they are still being used by users or a web application?
I found a forum ...
8
votes
3answers
684 views
Speed impact of using varchar in SQL Server 2008
In the old days, using varchar over char was a big no no, since the varchar was stored on a different page than the rest of the row. Is this still the case today with SQL Server 2008? Will I see any ...
7
votes
2answers
2k views
SQL Server 2008 - Partitioning and Clustered Indexes
So let me preface by saying I do not have total control over my db design, so a lot of the aspects of the current system cannot be changed for the purposes of this scenario.
Comments about how we ...
2
votes
4answers
2k views
SQL Server 2008 R2 Ent trial expired move to express?
So someone let our SQL server 2008 R2 enterprise trial expire on a web server. It was part of a plan to restart a project and move everything we had over to MySql instead. We don't need anything ...
5
votes
1answer
1k views
Print Parameters in Dynamic SQL
I've used dynamic SQL for many tasks and continuously run into the same problem: Printing values of variables used inside the Dynamic T-SQL statement.
EG:
Declare @SQL nvarchar(max), @Params ...
3
votes
2answers
539 views
DMV Queries as Perfmon Counters
Certain DMV queries that return various statistics that increment since boot would be useful as perfmon counters.
For example, take:
SELECT wait_type, wait_time_ms FROM sys.dm_os_wait_stats;
Most ...
5
votes
2answers
4k views
How do I switch off SCHEMABINDING for a view without recreating it?
How do I switch off SCHEMABINDING for a view without recreating it?
3
votes
2answers
811 views
tsql: table data size won't shrink after large row deletion
i have a table with 12 months+ data in it.
I backup the table then delete all rows keeping only rows inserted in the last month.
But the table size won't shrink, nor the underlying physical file.
...
4
votes
1answer
542 views
Impact of an Asynchronous Mirror / Full Recovery Model on Performance
For an asynchronous mirror in SQL Server 2008 R2, using the full recovery model is required.
Assuming that the network and disk IO on both sides of the Mirror can keep up with the transaction logs ...
6
votes
1answer
533 views
sp_WhoIsActive and get_plans — Actual Plan?
Does Adam Machanic's sp_WhoIsActive return the actual query plan or the estimated plan when run with @get_plans=1 ?
12
votes
5answers
2k views
Why use an int as a lookup table's primary key?
I want to know why I should use an int as a lookup table's primary key instead of just using the lookup value as the primary key (which in most cases would be a string).
I understand that using a ...
9
votes
2answers
460 views
A Non-DBA Asks: How to Painlessly Copy/Move a SQL Server instance to another Server via Backup/Restore?
We've just got a new server so I need to copy the existing SQL Server 2008 instance over from the old db box.
I normally do this by copying .mdf and log files over and attaching them but I'm not able ...
14
votes
3answers
7k views
Execution Plan Basics — Hash Match Confusion
I am starting to learn execution plans and am confused about how exactly a hash match works and why it would be used in a simple join:
select Posts.Title, Users.DisplayName
From Posts JOIN Users on
...
4
votes
5answers
5k views
User Permissions Messed Up following a Backup -> Restore Operation
I had to move several SQL Server 2008 databases to our new db server so I backed them all up (to .bak files), copied these files over to the new box and restored them (all done using SQL Management ...
2
votes
3answers
615 views
How does NHibernate handle execution plan?
I heard from a podcast that there are no ORMs that have a good solution for execution plan reuse. It will lead to increased execution plan cache which affects the performance.
How does NHibernate ...
3
votes
1answer
920 views
When should I use Full-Text Search?
When should I use Full-Text Search?
Instead of using SELECT * FROM myTABLE WHERE myCOLUMN LIKE '*something'
3
votes
2answers
277 views
Resources for SQL Server developer migrating to Oracle
What resources can you recommend for a SQL Server developer wanting to learn Oracle basics?
I am looking for a comprehensive whitepaper or blog post describing differences between these systems and ...
3
votes
1answer
426 views
Oracle query against SQL Server database giving inconsistent results
We have multiple Oracle 11.2.0.2 databases with og4odbc database links to a SQL Server 2008 database. A particular table in the SQL Server database has two Varchar(36) columns. The following ...
1
vote
1answer
498 views
SQL Server 2008 R2 - how to repair System Views
I have SQL Server 2008 R2 database, which is corrupted. Dbcc checkdb command gives result:
Msg 8921, Level 16, State 1, Line 1
Check terminated. A failure was detected while collecting facts.
...
1
vote
2answers
412 views
SQL 2008 - DBCHECK Failed
I am using SQL Server 2008 and have a Maintenance Plan setup that does a DBCHECK before the nightly backup.
This has been working fine for months. It has started failing. If I run a DBCHECK on the ...
3
votes
3answers
512 views
ms sql differential backup size jumps
We are doing a full backup once a week and a daily diff between that. The total db is 80Gb, the diffs start out under 1Gb, then gradually grow to approximately 3-5Gb which is ok, but then all of a ...
3
votes
3answers
2k views
Port number change
We are planning on setting a static port for SQL Server 2005/2008 failover clustering. Any guide on what port to choose/select for 4 nodes of each cluster?(Active/Active)
I am also thinking some ...
3
votes
1answer
3k views
How do I calculate values based on the previous row after skipping the first 12 rows?
I'm looking for help to write this query. I have included some sample data below. My initial premise is that all the values from the 13th row onward will be dynamic and the first 12 rows will be ...
2
votes
2answers
1k views
SQL 2008 R2 3rd Party Peer-to-Peer Replication, Global Site Distribution
We are looking at hosting 3 globally distributed SQL Server installations at different data centers. The intent is that Site A will serve web traffic and data for a specific region, same with Site B ...
8
votes
4answers
4k views
Methods of speeding up a huge DELETE FROM <table> with no clauses
Using SQL Server 2005.
I am performing a huge DELETE FROM with no where clauses. It's basically equivalent to a TRUNCATE TABLE statement - except I'm not allowed to use TRUNCATE. The problem is ...
2
votes
1answer
215 views
Are there other sites still using SQL Server with codepage 850 or codepage 437 for their sql scripts?
Using one of these codepages is a disaster when scripts are edited by Visual Studio or TFS, but sometimes it is difficult to change old standards.
What must be observed, when changing to the current ...
9
votes
1answer
449 views
SQL Server 2008 R2 Dirty reads - how non-atomic?
I am wondering "how dirty" dirty reads can get under a read-uncommited isolation level. I understand that rows that have been updated but not yet committed are visible, but:
Can a row appear as ...
4
votes
1answer
326 views
convert legacy showplan text into graphical format
Does anyone know of a way to convert legacy showplan text into a graphical format?
In other words, can I take the StmtText returned from SET SHOWPLAN_TEXT ON and convert that to show me a graphical ...
3
votes
2answers
692 views
Should we use SQL Server partitions for a multi-tenant database design?
My organisation is creating a multi-tenant data architecture to support analysis of data uploaded by multiple different clients. The database is Microsoft SQL Server.
It has been suggested that ...
