Tag Info

New answers tagged

3

we're going to allow an external partner (i.e. not an employee of our org) access to this server, like so: Their credentials will own (belong to db_owner) a couple databases. A concern along with the service account being the same on multiple instances is that an outside entity can escalate their permissions quite easily. Given that outside entity is ...


3

Risk mitigation would indicate creating a separate account for each service on each machine. The level of work required to create the accounts necessary is extremely minimal, but the unknown risks that accompany not doing so are quite high, according to Microsoft's own recommendations. Microsoft Best Practices recommend using separate service accounts for ...


-1

as far as i can see this is the answer select case when money = 1200000 then '12 Lc' when money = 12000000 then '1.2 Cr' else '' end as column from table ....


0

When processing all dimensions and facts in SSIS separately you do it in more than one transaction. Processing the whole cube in one transaction is faster when the server can handle it.


1

Yepyep, bring the data from two servers to one server, then do your join there. When I set this up, the business were adamant it was a once-a-month report, turns out it was weekly... So I setup a SQL job that moved data from one server to the other, into a staging database. Job would truncate the staging table, then be a SELECT INTO grabbing only the data it ...


2

I've used this technique in data synch jobs. It helps if you don't need the entire table from the remote server (just a few columns or you can filter it down in your WHERE clause). And, by bringing the data into a temp table on the instance where you're doing the most work, you can index the temp table to your needs and get statistics that are helpful to ...


0

Check the details in the Create an Extended Events Session Using Query Editor MSDN article. It's for SQL Server 2012, but it should be valid for 2008 too; the ALTER ANY EVENT SESSION permission should be granted.


0

Not knowing much else about your problem, if your database is online and you're not using anything that may be actively using part of the log - such as Mirroring, AGs, Replication, etc, you could attempt removing that part of the log from the sweep by changing to the simple recovery mode (if in bulk or full) and issuing a checkpoint. Then go back to your ...


0

In addition to the above suggestions, have you also duplicated the tempdb configurations? It may be that your previous server had multiple tempdbs configured, while your new one only has one. If you need to configure them, have a look here :


0

It was an issues on the hard drive. It never showed any errors in event viewer or the controller. When we rebooted the server, the errors showed up and we replaced the drives.


0

You can use a case statement for this, if it must be formatted in the database and you can't persist it. Select case when len(facilityCode) = 1 then '0' else '' end + facilityCode as facilityCode From YourTable It sounds like you are doing this as a FK, if so you can't have a FK constraint on a calculation. To make doing the join easier, you ...


1

One important thing with shrink operation when Shrinking through management studio is the option 'Release unused space' what this does is ''Cause any unused space in the files to be released to the operating system and shrink the file to the last allocated extent, reducing the file size without moving any data. No attempt is made to relocate rows to ...


2

The rule is not to never shrink. The rule is to not shrink when you will need to grow again. Go ahead and shrink to reclaim the space. Deal with the resulting fragmentation. Be aware that shrinking can cause extreme disk load. These are pretty much the only problems resulting from shrinking. Or, rebuild all indexes into a fresh filegroup and drop/truncate ...


2

I recall that the rule of thumb used to be "never shrink databases". Does it apply in this case? Yes. Dont touch that shrink button - Its a general rule and a very very sound advice from many renowned SQL Server Gurus - Paul Randal, Brent Ozar, Mike Walsh, Gail Shaw and many more. The database is still in use, and still somewhat growing, but I do ...


1

(it looks from your example code that you are using MS SQL Server, but you should add an appropriate tag (and/or body text) to your question to indicate this if so, or if not which DBMS you are actually using) Any of the options above should work fine, I would generally use the "prefix 0s then use RIGHT() option as you have but the others are perfectly ...


7

The safest way is probably to only add zeroes when the length of the column is 1 character: UPDATE Table SET MyCol = '0' + MyCol WHERE LEN(MyCol) = 1 This will cover all numbers under 10 and also ignore any that already have a leading 0. EDIT To just select the data try: SELECT CASE WHEN LEN(MyCol) = 1 THEN '0' + MyCol ELSE MyCol END FROM ...


0

UPDATE MyTable SET MyColumn = '0' + Mycolumn WHERE MyColumn < '10' AND MyColumn NOT LIKE '0%';


1

If you are using WMI event alerts for server events then service broker is not required as it uses Service Broker SQL/Notifications/ProcessWMIEventProviderNotification/v1.0 in msdb and Service Broker is enabled by default for msdb for 2005 and up. Read up on - understanding WMI provider for server events To Answer your questions : But I see lot of ...


2

Index reorganize does not touches statistics so there is no chance for causing recompilation. Since when index is rebuilt with full scan stats are also updated for the column this can trigger recompilation as statistics change.


0

DECLARE @submit_date DATETIME; SET @submit_date = '2014-07-24 11:10:41.403'; SELECT 'Posted ' + CONVERT(VARCHAR(40), DATEDIFF(minute, @submit_date, GETDATE())/(24*60)) + ' days, ' + CONVERT(VARCHAR(40), DATEDIFF(minute, @submit_date, GETDATE())%(24*60)/60) + ' hours, and ' + CONVERT(VARCHAR(40), DATEDIFF(minute, ...


2

As I said in the comments above, this should really be formatted in the application layer, where you are controlling the presentation to the user. The issue lies with the way SQL handles the DATEDIFF function, in that comparing the HOUR difference between 08:59 and 09:00 will result in 1 hour, because the hour has ticked over and there is 1 hour difference ...


0

select *, Days = datediff(dd,0,DateDif), Hours = datepart(hour,DateDif), Minutes = datepart(minute,DateDif), Seconds = datepart(second,DateDif), MS = datepart(ms,DateDif) from ( select DateDif = EndDate-StartDate, aa.* ...


1

You DB should probably not be publicly accessible, unless it really has to be. You can make DB accessible only to localhost, which would allow your web server to talk to it and you can use it over SSH or Remote Desktop or what have you. You'll want to watch out for brute-force attempts on your logins. I'm not sure what the Windows equivalent of Fail2Ban is, ...


0

I would try to answer the questions point wise After few days if i check in task manager, it shows the load of 31 GB Memory but actually maximum is alloted to SQL is 28 GB and nothing is running on he server First please note that task manager is not correct place to look for SQL Server memory utilization especially when SQL Server service account has ...


1

So in this case it does seem to have been the 8 tempDB files causing the biggest issue. I ran the analysis suggested here http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-1230-tempdb-should-always-have-one-data-file-per-processor-core/ and found no PAGELATCH issues and a very high proportion of PAGEIOLATCH waits (I don't remember exactly, but ...


2

Although you limited the memory to 28 GB out of 32 GB, this is not the only memory that SQL Server uses. The running programs and needs within Windows take some additional space. If you want to closely tune the memory use, I recommend reading this, which works well for my servers. See Jonathan Kehayias at: ...


0

You could rewrite with a LEFT JOIN: SELECT IT.[iInternalTransactionID] , IT.[iInternalAccountID] , C.[iContractID] , IT.[dtTransactionDate] AS TransactionDate , (CL.[sFirstName] + ' ' + CL.[sLastName]) AS ClientName ... -- ### unchanged up to here ##-- , G.TermNo , G.TermNo * G.MaxMonthlyAmount AS TotalAmount -- ### end ### -- FROM ...


0

Since you have identified that the sub-select (SELECT ...) CPH cannot join with higher level nesting, how about changing your sample rewrite as something like: INNER JOIN ( SELECT MAX([iContractID]) AS [iContractID] , MAX([iTermNo]) AS [iTermNo] , MAX([cMonthlyAmount]) AS [cMonthlyAmount] FROM [tbl_ContractPaymentHistory] WHERE ...


0

@Alan Please check once by disabling the audit. That server has an AUDIT on a database. If the audit is enabled, you will get that error. If you disable the audit,query will run fine.


0

Provisioning the SQL Server Agent service account is simply referring to "configuring it". Which you stated you changed the service account from Network Services to Local System, that is provisioning the service to run as "Local System". It is just terminology used in their documentation. Regarding the issue at hand, you need to review the system log within ...


1

You will need to manually merge the existing databases and initialize from that source. One challenge you might face in manually merging the databases is conflicting primary keys. If so, this is typically handled by adding a location-specific identifier column and extending the primary key to include this column. This is known as partitioning your data.


0

Assuming those objects are stored procedures or views (and not tables), you'll need to: script the objects in question drop them from the database (or just remove the schema-binding) change the database collation recreate the objects by running the scripts you created in the first step. As an aside, you didn't need to sp_msforeachtable "ALTER TABLE..." ...


1

The problem was in the query, it seems that it doesn't work in parallel, Here is the parallel version by @usr (Source for query: stackoverflow.com/a/24810980/122718) that do max ALL CPUs to 100%: USE master SELECT MyInt = CONVERT(BIGINT, o1.object_id) + CONVERT(BIGINT, o2.object_id) + CONVERT(BIGINT, o3.object_id) INTO #temp FROM sys.objects o1 JOIN ...


1

It depends on when the trigger fires during the transaction. The trigger itself is part of the transactional context you are currently in. This means that any changes you have already made in the transaction so far will be visible to the trigger. To take a simple example, consider this data model: CREATE TABLE Foo (F INT) CREATE TABLE Bar (B INT) INSERT ...


0

Never do shrink database as it increases fragmentation even more. And yes, it looks like sizes are different because of fragmentation, so you have lots of space that stores nothing (half filled pages). This is probably because of incorrect schema design and wrongly chosen clustered indexes. But fragmentation happens on production system and it's good idea to ...


0

Let me answer your question point wise 1.SQL Server express has SQL server agent code built in but disabled. When you go to sp_configure you can see Agent XP enable/disable option this is so because if any point of time you are planning to upgrade SQL Server Express Microsoft upgrade process does not have to do much change but just a minimum to enable SQL ...


1

I would store this as two different INT/BIGINT, one for each side of the decimal point. To make it easier to query, you can also add a computed column that renders the data as a varchar. In other words: CREATE TABLE MyData ( IntegerPart INT NOT NULL , FractionPart INT NULL /* Null of no fraction */ , HumanFormat AS CAST(IntegerPart AS VARCHAR) ...


1

If you want to see pain points of a query capture the actual execution plan via SSMS when you run it. Viewing this within SQLSentry Plan Explorer (free!!) is a bit more easy on the eyes than what SSMS will show. There are some good blog post and articles scattered around the Internet on how to read an execution plan if you need it. Capture Actual Execution ...


0

I suspect it is just timing out if you are using SSMS right click. Need to run the rebuild as TSQL statement. You can right click SSMS and get syntax for drop and rebuild indexes. As for size a fragmented index takes more space. Given the index rebuilds are timing out and you have the large difference in query time you more likely have severely fragmented ...


1

I must say first that using fn_dblog and fn_dump_dblog is unsupported as these commands are not supported by Microsoft. Jonathan Kehayias in his article even pointed out that using fn_dump_dblog multiple times has grave performance implication so use these undocumented commands on your own risk. There is also a way to track page splits, not perfectly though ...


0

After trail and error i managed to use the following sql to find the reason. SELECT OBJECT_NAME(p.object_id), i.name, COUNT(dblog.AllocUnitId) [count] FROM sys.partitions p LEFT JOIN sys.indexes i ON i.object_id = p.object_id AND i.index_id = p.index_id INNER JOIN sys.allocation_units au ON au.container_id = p.hobt_id INNER JOIN fn_dump_dblog ( ...


0

"Throw more hardware at the problem" will not always solve theses kind of issues. What if Paul adds 100Go to his log space, and it still gets filled because of poor programming techniques? Also, adding more space may not be feasible, for example if his Servers are virtualized and the hardware running it is at capacity. Given it is a developper's environment ...


1

For a database in SQL Server, there's a setting called Auto-Close, which is meant to minimize the amount of resources used while SQL Server is running, but no users are actively doing things. This setting is enabled by default in Express Edition instances, as those are usually running on developer workstations where performance isn't critical, and resources ...


0

Looks like the disk subsystem on Server B is performing worse than on Server A but, I used to see disk issues not entirely related to disk specs. You could collect some other performance counters such as physical disk --> avg disk sec write (> 25 ms very slow), memory --> page file usage (> 70% bad), cpu --> processor queue length (> 12 very bad), memory --> ...


8

nvarchar(8) PK NOT NULL ... their PKs return null ... This cannot happen, under any condition. Therefore I must conclude that your 'simplified' repro has simplified things beyond the limit of what you understand. Please post the exact table definition (including all indexes) and the exact query you issue. Null is what comes back from the linq ...


5

If you're using READ UNCOMMITTED isolation level or the WITH (NOLOCK) in your queries, your queries can skip rows or see rows twice. Kendra Little explains it in more detail in her 30-minute webcast There's Something About Nolock.


0

If the following command is run by someone with the authority to do so, the trace will be stopped. exec sp_trace_setstatus @traceid=<trace_id>, @status=0 -- Stop the trace However it still exists in the stopped state. This is the state you are reporting as 'Paused'. If the stopped trace should be ended, then a second command is needed: exec ...


0

The ndf file is an additional file, which you can add to your database in the database properties. You have to think about files and filegroups as well. Otherwise you are not able to control, what is stored in which file. Multiple files can be used mainly to distribute load on your IO system, allow you to implement partial backup strategy for large databases ...


2

NDF is secondary data file, By default it will not be created unless we create the file while creating SQL server database or manually create it after database is created using AlTER DATABASE statement. SQL server by default will create one primary data file and one log file. If it is not visible in sys.master_files means you have not created it. Read more ...


0

You could use a FileSystemWatcher which triggers when the Excel file changes and runs simple code to push the revised file through to SQL Server.



Top 50 recent answers are included