Tag Info

New answers tagged

5

This should put you in the right direction: DECLARE @results TABLE ( database_name sysname, db_user_name sysname, login_name sysname ); INSERT @results EXEC sp_MsForEachDB ' USE [?]; SELECT DB_NAME() AS database_name, dp.name AS db_user_name, sp.name AS login_name FROM sys.database_principals dp INNER JOIN sys.server_principals sp ...


7

You should be able to do something like this EXECUTE AS login = 'loginname'; SELECT name FROM sys.databases WHERE HAS_DBACCESS(name) = 1 For reference see EXECUTE AS and HAS_DBACCESS


0

There isn't a standard option to do this, but you can achieve this several ways. create a separate db and move your log tables there. set a max file size. create a trigger which checks row count or table size and limits inserts when value exceeded and/or deletes old data. create a foreign key constraint on an id column pointing to a table with a set number ...


0

Answer removed from thread: Help with SQL Server Error “ad Hoc access to OLE DB Provider 'Microsoft.ACE.OLEDB.12.0'”


2

One thing to note is that RESTORE is a command, not really a permission that can be granted. Which is why your GRANT failed. In the SQL Server Books Online at https://msdn.microsoft.com/en-us/library/ms186858.aspx has pretty much said the same thing from SQL Server 2000 until now, namely: RESTORE permissions default to members of the sysadmin and ...


2

I think that the most difficult part of at least becoming a pretty involuntary database administrator is the fact that you have to bear whatever happens to the databases of the specific organization that you have happened to stumble upon. In my experience, my first shock was on a Monday morning when the database server crashed because of a seemingly ...


3

I would like to know if it is possible to share the structure and data between the two systems without duplicating and/or having to copy everything each time I load a new OS. Effectively, I would like all the data to be available to Ubuntu when it is created in Windows, and vice versa. You can't. Just not supported, not possible. No can do. PostgreSQL ...


0

I think the answer depends upon how your app is intended to work. Do you envisage users being able to reply to many messages? Or only to a single message? Personally id keep all messages in a single table with a self join. (Like a employee heirachy where each employee has a manager). To speed up searching and selection you could keep all related replies ...


0

I would suggest against using a serialized array, but instead encourage you to build the functionality into your database so that it can be more easily expanded and managed. Given that you're considering implementing a serialized array, I assume that messages can be a reply to zero or more other messages. In this case, a linking table for replies would be ...


0

The database has log and data files that are stored on the file system. If the sys admin has a process of regularly backing up the location where the database files are stored then you might be able to get some of the data back. Assuming that you can get these files from before you overwrote them - then create a new database and attach the db and log file to ...


0

it seems like your only option is a super long shot hail mary. WITH REPLACE replaces the old DB but I'm not sure if it explicitly searches for those DB pages on disk and tries to overwrite the pages even if the DB is different. If the data is only 'marked' for deletion from the OS then you can have a very expensive data recovery center look at it. ...


0

Look to see if the data is stored somewhere else. Perhaps the system sends email confirmations. If so, retrieve the emails (from sent items or from auditing on the email system) and hire some temps to retype the information. Perhaps the system prints out reports. If so, obtain the printouts and get the temps typing. Perhaps the system exports data and ...


0

I would like to inquire if there is an possible way to return lost data that stored in a Sql server Database after making a wrong database restore? No. There is NO way to get the data back. You might have used RESTORE .. with REPLACE which REPLACES the data in the database with the data from the backup that you just restored. So there is no way. You ...


0

No backup, its almost a zero chance of recovering the original data: Only thing can help if you have anyone of these like snaphsot, ,mirroring, LOGshipping or replication enabled for that database. Have not heard, a roll back without proper backups!


0

((100G is not the same as 100M. typo was fixed)) The line you gave is what you do -- in my.cnf. (Double check the syntax.) That configuration was very important 15 years ago -- when some operating systems could not create a file bigger than 2GB (or 4GB). Now is is just an artifact. Most people simply have autoextend on the last entry and forget about ...


0

No, the skill sets are not too separate at all. As others have indicated knowledge of AD, group policy, authentication protocols, programming, the fundamentals of database design and querying are all important and make you both more effective as a DBA and more marketable. Also, ignore your knowledge of storage, it's configuration, and performance at your ...


4

As a database administrator, you are part of a team whose goal is to make a job easier, or just possible, for someone. The team includes system administrators, database administrators, database programmers, application programmers, network administrators, and so in. A valuable team member knows not only their own role but a little bit about every role in ...


1

CAST() might not be needed, try with and without it. SELECT student.StudentID ,student.NAME ,CONCAT ( cast(ROUND(Count(attendance.AttendanceID) * 100 / 6) AS CHAR(20)) ,'%' ) AS Total ,student.WorkshopID FROM student LEFT JOIN attendance ON student.StudentID = attendance.StudentID GROUP BY student.StudentID ...


0

I don't think the issue is InnoDB table size limit at all. I have posts about InnoDB Tables having a Table is Full Condition in the InnoDB Plumbing May 31, 2013 : How to solve "The table ... is full" with "innodb_file_per_table"? Mar 31, 2014 : mysql directory grow to 246G after one query, which failed due to table is full My guess ...


0

You could also use MySQL events (like internal cron) and select into outfile. Advantages of this method? Your backups will contain this logic. Both approaches are valid IMO just depends on your preference.


0

To check this out, I did the following - I created a file called my_script.sql [pol@localhost inst]$ more my_script.sql use test; select * from example; [pol@localhost inst]$ Then ran the following [pol@localhost inst]$ ./bin/mysql -S ./mysql.sock -u root -pdba < my_script.sql > xfile Warning: Using a password on the command line interface can ...


0

After talking to someone more knowledgeable then me I have came up with the following answers to my original questions. TLDR; Cron should be fine for what your doing but don't write a bash script, write a PHP script instead, it's better. Q1. Do I have to worry about putting the database username/password into this >cron script? A1. As long as ...


5

Check if another process has claimed port 1434: netstat -ano | findstr 1434 This will give you the process id of the offending process, which you can then identify: tasklist | findstr /c:"<process id>"


2

The typical cause for this situation is a query that locks one or more tables and is stalled somehow (or just takes for ever). Other requests from separate sessions keep piling up and have to wait for the lock to be released - until you hit the connection maximum. Generally, connection pooling can prevent this situation from stalling your whole server. If a ...


0

is it simply just changing the Database Read Only State to "True"? yes, you can use below t-sql alter database db_name set read_only with rollback immediate Also, is it a good idea to change the recovery model for this old production database to "Simple" vs "Full"? Yes, a read-only database wont be having any DML operations. So best practice is ...


-4

If you only need to eliminate the last rows which have not followed the incremental value of an identity field, there is an easy and safe way: first delete the last record(s) which have 'jumped' change the data type of your Identity Field (from int to bigint or vice versa) save the table add a new record, and check it assigns the number of the highest ...



Top 50 recent answers are included