Tag Info

New answers tagged

0

Let me do a small summary here: The Average Disk Queue Length is about read and write operations on your hard disk. The goal is to minimize these operations, you want the data to be read from memory since it's way faster and efficient. The recommended value for RAM on an SQL Server is minimum 50% of your databse size. And of course you should reserve at ...


1

SQL Server will reuse available space in the datafile as necessary. If there is no space left it will attempt to auto-grow the datafile(s) for that File Group based on the auto-growth setting. If auto-grow is disabled and there is no room left, then you will get an error trying to do something that would require more space. With regards to deleting large ...


0

The mirror state is likely confused at this point and should not be trusted. cherish.j.wang posted the following at: http://www.sqlservercentral.com/Forums/Topic1237273-1549-1.aspx use master GO alter endpoint endpoint_name state = stopped; GO alter endpoint endpoint_name state = started; GO In that same discussion Robert Davis (SQL Soldier) agreed that ...


4

The SELECT ... INTO syntax is only for new tables. I think you want: INSERT dbo.TableB ( <column list> ) SELECT <column list> FROM dbo.TableA WHERE ID IN ( SELECT ID FROM dbo.TableC ) ;


1

The easiest way I know of is creating a table in tempdb with a single column (id for instance), then expand the tempdb node in SSMS and find the table. Right click and select "Edit top 200 rows". A grid will appear. Select the whole first row with the row header on the left, then paste the whole contents of the file. BCP is an alternative way to load the ...


0

/********************************************************************/ SELECT tst.[session_id], s.[login_name] AS [Login Name], DB_NAME (tdt.database_id) AS [Database], tdt.[database_transaction_begin_time] AS [Begin Time], tdt.[database_transaction_log_record_count] AS [Log Records], tdt.[database_transaction_log_bytes_used] AS [Log Bytes Used], ...


3

You could use bcp to write the rows you wish to retain to a different server. Then truncate the table (or drop and re-create). Use bcp a second time to import the data previously exported.


6

As things stand, the query: DELETE TOP (4000) FROM [OLD_TABLE] WITH (TABLOCKX) WHERE [Date] < '2014-01-01 00:00:00' ...has to scan the whole heap table, testing each row it finds, until it eventually finds 4000 to delete. On the next iteration, the whole business starts again from square one. Assuming the scanning process is performed in the same ...


5

I did not find correct answer posted so I will try to explain you in details, bear with me. First whenever you post question please post version and edition of SQL Server you wont believe answer might change completely with different versions Lately we've noticed the VM Committed memory space for our SQL Server 2005 instance grows and it's almost at the ...


1

There are three ways to find who is connected to a database in sqlserver. First one: Use the SQL SERVER Management Studio -- Management -- Activity Monitor This gives a list of users, database names, status, command, transactions, application with which they are using the database, CPU, IO and Memory usage, Login time etc. Second One: Use the built in ...


0

Could you post the following result from the Query?These points I have provided below may help you diagnose the issue Script to report Memory usage details of a SQL Server instance Original Author Sakthivel Chidambaram This Script will help you to diagnose the issue ...


1

Start by checking max memory. Instructions here: http://technet.microsoft.com/en-us/library/ms191144(v=sql.90).aspx You should set the max to be low enough to leave space for the os and any other apps. Which in your case is going to be very low. Maybe 500mb-1gb


1

If it was possible to connect to a SQL Server without setting up a security context on that server, that would be a serious security hole. So, there must be a security context. If you can ride on some existing rights that are already defined, then fine, but likely any existing security context was not tailored for your need. And since you want a specific ...


0

No, there isn't a way to separate the two. You'll have to either hang on to the large file, or take a new full backup and start moving forwards from there.


3

First, sp_change_users_login is deprecated feature and is discontinued in SQL Server 2014. Secondly, EXEC sp_change_users_login @Action = 'Report' is not a proper way to get orphan users list. As with 'Report' parameter, it will execute following query. According to which it will return users where issqluser = 1 (It must be a SQl Server Login), and no ...


0

Check that if you have indexes (clustered/nonclustered, full text). Use sp_spaceused 'your_table_name' to check that you can actually discard indexes. Check what type of table is used. In your example of 0.2KB=205 Bytes you will have 38 rows per data page if your table is heap and 39 rows per data page if it is clustered table. Please see below the ...


0

Likely I may be interpreting the data incorrectly however this is what I did : Ran the following query ( referenced from http://stackoverflow.com/questions/7892334/get-size-of-all-tables-in-database) between 2 time intervals. Between the two runs of the query, we compared for the table(s) in question number of rows (RowCounts) that were added and the used ...


8

There are two key differences between EXCEPT and NOT IN. EXCEPT EXCEPT filters the DISTINCT values from the left-hand table that do not appear in the right-hand table. It's essentially the same as doing a NOT EXISTS with a DISTINCT clause. It also expects the two tables (or subset of columns from the tables) to have the same number of columns in the ...


2

If I can understand correctly what you are looking to do, you want to SELECT all records from TableA that only appear once, but don't already appear in TableB. You can do this with a GROUP BY and HAVING to identify those that only appear once in TableA, then do an EXCEPT to filter out those that already appear in TableB. SELECT int, com FROM TableA ...


3

--======================================================= -- delete the duplicate records from table @t -- keeping a single unit of each -- marcelo miorelli 24-nov-2014 --======================================================= --======================================================= --create a table variable and insert records in it -- just for this ...


2

select ROW_NUMBER() OVER (PARTITION BY num, com ORDER BY identity_field) as 'RowNumber', t1.* into ##TableA from TableA t1 --delete t1 select t1.* from TableA t1 join ##TableA t2 on t2.identity_field=t1.identity_field where t2.RowNumber > 1 but first make backup of your table


1

Add role member SQLAgentUserRole in msdb, the user will receive access only to the local (owned) jobs. -- Step 1 CREATE LOGIN [AgentJobUser] WITH PASSWORD=N'1234', DEFAULT_DATABASE=[msdb], DEFAULT_LANGUAGE=[us_english] , CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF GO --Step 2 USE [msdb] GO CREATE USER [AgentJobUser] FOR LOGIN [AgentJobUser] WITH ...


1

User need access on that job, he can modify and run too. You can give the user - SQLAgentUserRole. From BOL : SQLAgentUserRole is the least privileged of the SQL Server Agent fixed database roles. It has permissions on only operators, local jobs, and job schedules. Members of SQLAgentUserRole have permissions on only local jobs and job schedules ...


1

On the question of the size of things, first of all you said that you shrank a database, so that would reduce the DB size of the space that was discarded during the shrink. John M also provided some clarification. Regarding the numbers in the bottom picture, they are: select (2585832 / 1024.0) /* KB from the Reserved in the bottom picture to MB */ ...



Top 50 recent answers are included